2023: Week 43 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Combine Inputs
The first step is to create a single table that contains the information from each of our input tables. We have an input for each of the 3 terms and another input with information around the year that the student is in.
First we want to combine the 3 term tables by using an union to 'stack' them on top of each other.
We then want to find the total number of days that a student was present and absent from class. We can use an aggregation step to group by first & last name then sum days present and days absent:
Finally we want to include the data around which year each student is in. This time we are going to use an inner join on both of the name fields:
After removing any duplicated fields the table should look like this:
Step 2 - Attendance Rate
Next we want to combine the first and last names so we have a full name field:
Full Name
[First Name]+" "+[Last Name]
Then we can calculate the attendance rate for each person across the year:
Year Attendance Rate
ROUND(
100*[Days Present]
/
([Days Present]+[Days Absent])
,2)
We are now ready to find the students with the highest overall attendance. Within a new clean step, we first want to rank each person based on their attendance:
Rank
We can then use this field to keep only the top value (Rank = 1), then we can remove the additional fields and our first output should look like this:
For the final output we need to create a new clean step from the step where we calculate the attendance rate. From here we can calculate the percentage ranking by utilising a percentile rank calculation:
Percentage Rank
We can then use this rank field to filter the value within the top 5%:
We can then output our second output that looks 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 @treamcelhone, @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!
