25 May 2012

Hints & Help: Analysis of New Intake by Postal Area


Recently we were asked if there was any way that Ensemble could show an analysis of new intake by postal area so. Clearly it’s important to know where your new students are coming from and perhaps even more important to know where they are not coming from so that steps can be put in place to address the lack of interest.

With Ensemble it’s a simple process to get a list of the entire new intake for a period of time. From the Pupils menu select the Pupil Extract option. On the second tab entitled Pupils, select the option for One Record per Pupil Activity. In the Starters/Leavers box select New Starters and select the relevant date range. Note you can select a specific period (term), a date range or a specific date. You should also select the type of record to include, i.e. new starters at school activities, centre activities etc.


You can add other criteria to the extract by selecting other options on this or other tabs but once you have set all the required options click on the Extract Data button. This will select all the matching records and display in Insight.

Note in the screen shot below I have used the Column Selector to remove all the columns we don’t need leaving me with just the Gender, Post Code and Activity. Notice also that as well as providing a column of information containing Post Codes the system has also provided a column of just post code areas. I have kept Gender and Activity so that we can also analyse by these values.


Now that we have the raw data it’s a simple job to group the data by Post Code Area and then create a New Summary Dataset of the result.


Once you have done this you will have a second dataset showing a breakdown of postal areas and the number of new starters.


If you want you can visualise these results in a graph by clicking on the New Chart button and following the steps. An example of this is shown below.


The above provides a good visualisation of new starters per postal area but say you wanted to go one stage further and show how these values are made up between boys and girls.

We have a column called Gender which contains F or M so we just need to reorganise the data we originally extracted to show a count of these for each Post Code area. We can do this in the following way.

Go back to the first tab where the extracted data was first shown and where you grouped the data by Post Code Area. You need to add the Gender column to the grouping but in order to do this you need to show the columns again. (They would have been hidden when you group by Post Code Area). To do this, click on one of the small “+” plus signs over on the left. This will expand the grouping and show the columns. From here drag the Gender column up into the group by area as before and then create another summary dataset by clicking on the summary dataset column. You will now have a new dataset as below.


We now need to Pivot the result so that we get count for each gender. To do this, click on the Pivot Table button and work through the wizard.

Select the Post Code Area as the Row Label.


Select Gender as the Column Label


Finally select _Count as the Summary Label.


Click on the Create button to create the new dataset which should look like this.


You can now use the chart wizard to create a new chart which will visualise this data. You will need to select a type of chart that will display the female and male values for each post code area such as a Bar or Column chart.


Select PostCodeArea as the Label column and tick both the available series columns in order to show both Female and Male values


The result is a chart that clearly shows the new starter counts for each post code area split between Female and Male. You can easily change the breakdown from Gender to some other category such as instrument by group by the appropriate column.

A word of caution though, not all postcode areas have the same number of pupils living in them and so an area showing a large intake may in fact have a large number of possible pupils in that area.

No comments:

Post a Comment