2023: Week 21 - Solution
Solution by Tom Prowse and you can download the workflow here.
We are revisiting The Prep School for the challenge this week and are looking at if the students grades are improving between years.
Step 1 - Reshape Data
First we want to get the grades in separate columns for each of the years so that we can compare them on a single row.
We can reshape the table by using a Columns to Rows pivot where we can utilise the wildcard pivot to first bring in all the 2021 fields, then we can use the + button to add a second pivot with all the 2022 fields:
We have renamed the fields so that it's easier to identify which grades are which. We can also remove the year from the Category field by using a custom split on the '-' and returning the 'last' section:
After this the table should look like this:
Step 2 - Average Grade
We are now in a position to calculate the average grade for each student in both years. We can calculate this using an aggregation step where we group by Student ID, First Name, Last Name, Gender, & D.O.B, then Avg 2021 & 2022 grades.
We can then calculate the difference between the yearly averages using a calculated field:
Now we have calculated the difference we can categories these into the Progress labels that were mentioned in the requirements:
Then finally we can use this field to filter and 'Keep Only' the "Cause for concern" rows.
After filtering we are ready to output our data that should look like this:
You can download the output here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @habeeb_gayle, @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!
