2023: Week 40 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Pivot Class
First we want to bring all of the classes into a single column so that we have a field for the Subject, Class or Dropped, and Student Name.
To do this we can use a Columns to Rows pivot where we use a wildcard pivot to bring in all fields with the word 'Class':
Step 2 - Group & Active Flag
Next we want to correct the spelling mistakes within the Subject field by using the in-built grouping features within Tableau Prep. We want to group by 'Spelling' which will group together the subjects that are spelt similarly into the correct spellings.
We can then remove any nulls from this list and this will give us all of our relevant subjects in the correct format.
After this we can create a flag to determine whether the class is active or dropped. To determine this we can remove the number from the Class or Dropped Class field, again using the in-built functionality within Tableau Prep.
We're now ready to use an aggregation to calculate the total per subject and active status. Within the aggregation we can group by Subject and Active Flag, then count the Name
At this stage our table should look like this:
Step 3 - Drop Out Rate
The final part of this task is to calculate the drop out rate for each class.
First we need to pivot the data so that we have the total count of active students and drop outs for each subject on a single row. We want to use a Rows to Columns pivot where we pivot by the Active Flag and Sum the Name field.
We can then rename the fields and calculate the following:
Total Enrolled
[Active] + [Drop Outs]
Then the drop out rate:
Drop Out Rate
[Drop Outs] / [Total Enrolled]
Our final output 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 @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!
