2023: Week 47 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Term Data
First we need to bring all of the data from the Term tables into a single sheet. We can do this by using the wildcard union within the input step that will allow us to bring the multiple sheets in and combine them together. Within the union we need to ensure that the worksheet filters contain the matching word 'Term*' so that it brings all tables with the word term in from the data source:
Step 2 - Parse Dates
Next up we can start to parse and format the date field so that we can create it as a date data type. First we can duplicate the Date and rename to Term Date, then to make sure we have a consistent pattern we can replace any '/' with '.':
Term Date
REPLACE([Term Date],'/','.')
We can then change this to a Date data type which will convert some of the dates, but some dates don't have a day associated with them so we need to handle these in a different way.
In this case we can use a calculated field to identify the null values and then add the days (16th) onto the original date field:
Term Date
IF ISNULL([Term Date]) THEN MAKEDATE(INT(LEFT([Date],4)),INT(RIGHT([Date],2)),16)
ELSE [Term Date]
END
At this stage the table look like this:
Step 3 - Student GPA
We can now start to calculate each student's GPA but first we need to pivot the data so that each of the grades are in a single column and totalled for each term.
First we use a wildcard columns to rows pivot with the matching pattern 'Subject':
Then to get a total across all subjects we can use an aggregation step to group by ID, Term Date, & Term then AVG Subject Grade (renamed to GPA):
Now we have the GPA, we can find the 3 term average by utilising the moving calculations with Tableau Prep. Within the Moving Calculation setup we want to group by ID, order by Term Date (asc), compute using GPA, and Avg the current and 2 previous values.
3 Term GPA Moving Average
Then round the value to 2 decimal places:
3 Term GPA Moving Average
ROUND([3 Term GPA Moving Average],2)
After this our table should look like this:
Step 4 - Student Info
Next we want to input the Student Lookup table and combine it with our grades data. First we want to create a student full name from the first and last name fields:
Student Name
[first_name]+" "+[last_name]
Then join this using an inner join the ID field to our grades data:
Step 5 - Output 1
After the join we can create a rank of the students so that we can see how the GPA changes over time. For this we can create a rank calculation where we group by the full table, then rank the Students (desc) and the Term Date (asc):
Rank
Then we can output the first table:
Step 6 - Output 2
Going back to the join, we can create a new branch where we can filter to keep only terms 3 & 6. We can do this by selecting both within the profile pane and then selecting keep only.
Then we want to calculate the percentile based on the 3 term moving average. For this we can create a rank calculation where we group by the full table, then use a percentile rank by ordering by the 3 term moving average (desc).
The second output 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!
