2023: Week 23 - Solution
Solution by Tom Prowse and you can download the workflow here.
The challenge this week comes from Andreea Scintei where we try to evaluate the quality of teaching based on surveys and results data.
Step 1 - Join Data Sets
First we want to input both tables from the input files and then join them using an inner join on Student ID:
Step 2 - Avg Grades
Next we want to calculate the average grade per class for each subject. We can do this using an aggregate step where we group by Class and Avg Economics, English, and Psychology:
At this stage our data table should look like this:
Step 3 - Pivot Data
We're now in a position where we can change the shape of our table by pivoting so that we have a column for Subject, Grade, and Class. To achieve this we want to do a columns to rows pivot where we bring Economics, English, and Psychology into the pivoted fields section:
Our table should now look like this:
Step 4 - Filter & Rank
The final steps are to calculate a rank based on the grade. Here we can select the rank calculation where we group by Rank and then order the Grade in a descending order:
Once we have calculated the rank we can then filter the table to the lowest performing class. In this case there are 6 in each so therefore we can filter to keep only 6 in the rank field.
We are then ready to output our table that should look like this:
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!
