2023: Week 26 - Solution

Solution by Tom Prowse and you can download the workflow here.

This is a follow on challenge from last week where we take a further look at analysing the admissions data for our schools. 

If you haven't completed Week 25 then go back and do that one first as we use the output as on of the inputs in this challenge.

Step 1 - Combine Additional Information

We want to combine both of our tables together so that we're working from a single table. To do this we need to extract the initials of the first and last names from the full name field. You can do this by splitting the name into separate fields and then use the Left() function, or you can do this in a single calculation: 

Initials 

LEFT([Full Name],1)

+

LEFT(SPLIT([Full Name],' ',2),1)

We can then join the tables using an inner join on Initials, Date of Birth, School Name, English, Maths, Science fields.

Step 2 - Ranking

Next we want to rank the students based on their grades, subject selections, and region. For this rank we want to group by Subject Selection and Region, then order by Grade Score (Desc) and Distance from School (Asc) 

Rank A 

We can then use this rank to create an Accepted or Rejected flag: 

Accepted or Rejected 

IF

[Rank A] <= 15 AND [Region] = "EAST" 

THEN "Accepted" 

ELSEIF [Rank A] <=5 AND [Region] = "WEST" 

THEN "Accepted"

ELSE 

"Rejected"

END

Then in our table we want to filter to keep only the Accepted students and the table should look like this: 

Step 3 - Region % of Totals

We now want to identify the top students and then ensure that they are split in the 75/25% split from the given regions. 

First we need to calculate how many students are from each school. We can do this by using an aggregate step where we group by Region and School Name then Sum Number of Rows:

Next we want to calculate the total spaces that are available within the region. We can use a Fixed LOD to calculate this by grouping by region then Sum Number of Rows: 

Spaces by Region

We rename the Number of Rows field to Total Accepted, then we can calculate the % of total each school takes up within the region: 

% of Total within Region 

100 * ([Total Accepted]/[Spaces by Region])

At this stage we can rename some fields so our table looks like this: 

Step 4 - School Performance

Next we want to identify the high performing schools and label them with a School Status. To do this we first need to identify the min and max % for each region, we can do this with another LOD calculation where we group by Region and find the Min & Max from the % of Total within Region field: 

Min per Region 

Max per Region

We can then use these fields to identify the highest and lowest performing schools and give them a flag using the following calculation: 

School Status 

IF [Max per Region]=[% of total within Region]

THEN 'High Performing'

ELSEIF [Min per Region]=[% of total within Region]

THEN 'Low Performing'

ELSE 'Average Performing'

END

From this table we only need the School Status and School Name fields and can remove all of the others so our table looks like this: 

Finally we can add the school status onto each of the students names by joining the workflow back to the step before the aggregation by using an inner join on School Name. This will bring back all of the student information whilst adding the school rating onto each of them: 

We are now ready to output the table that should look like this: 

You can download the output from here.

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!