2022: Week 35 - Solution
Solution by Tom Prowse and you can download the workflow here.
This week is an extension to week 34, and we used the same data source to take our analysis a bit further by focussing on the overall performance from spin classes. We want to find some key stats to help us compare against the previous year.
Step 1 - Split & Convert
The first step this week is to prepare the data so that we have the correct metrics in our table. We did a similar task last week where we want to split the F5 field using a '-' so we get a column for Coach, Calories, and Music Type.
We can then rename the Value field to Mins and remove Units, Type, and F5. At this stage our table should look like this:
Step 2 - Calories & Speed
[Calories]/[Mins]
Then we need to convert our dates into just years. This can be done by using the Convert Dates functionality within Tableau Prep.
Then we need to create a parameter so that we can estimate the speed as this isn't given within the data. The parameter can be a whole number with all allowable values that is required when we run the flow.
Now we have these metrics, we can now calculate the distance using this calculation:
Distance
([Mins]/60)*[Parameters.Speed (kph)]
Our table should now look like this:
Step 3 - Creating Aggregations
We now want to create the aggregations that are provided in the requirements. As there are a couple of different levels, we need to split the workflow into two branches. One for aggregations just at the Year level (overall metrics) and two for aggregations at the Coach and Year level.
Year Branch
Within the first aggregation we want to group by Date and then bring through the metrics Sum Distance, Sum Number of Rows, Avg Calories, Avg Calories per Minute, and Sum Mins:
After the aggregation we want to rename the following:
- Number of Rows to Total Rides
- Calories to Avg. Calories per Ride
- Mins to Total Mins
- Calories per Minute to Avg Calories per Minute
- Distance to Total Distance
From here we want to change the shape of our data, so will need to use a pivot for this. We want to bring each of the metrics into a single column by using a columns to rows pivot with each metric in the pivot:
This results in three columns:
We can then rename the Pivot Names & Values field, and exclude the 2020 value from the Filter field. The final task is to round the Value to 1 decimal place and our table should be ready to go.
Value
ROUND([Value],1)
After rounding the values to 1dp, our table should now look like this:
Coach & Year Branch
We now need to go back to the first step and create a second branch where this time we want to group our aggregations by Coach & Date.
This time we want to group by Coach & Date then Avg Calories per Minute, and Sum Mins.
After renaming the Pivot Names & Value fields, our table should look like this:
From here we then want to identify the maximum values per Coach so need to create another aggregation to find the Max Values:
Next we want to the rename the Value to Max Value and then filter to exclude the year 2020.
From here, we can join these max values back to our original workflow (Step before Max aggregation) so that it acts as a filter to only return the coaches with the max values.
We want to join where Max Value = Value and Measure = Measure. This will exclude lots of values but leave us with a filtered list of only the maximum values:
The step on this branch is to remove any unneeded fields, including Date-1, Measure-1, & Max Value.
We then want to ensure the Value field is rounded to 1dp using the same calculation as before - ROUND([Value],1)
We then want to ensure that we are including the Coach name in each of the values, and formatting the numbers to be in brackets. We can do this with the following calculation:
Value
[Coach]+' ('+STR(ROUND([Value],1))+')'
Then finally we can remove the Coach field and rename the Mins in Value to Total Mins per Coach. After this our table should look like this:
Step 4 - Prepare Output
We now have all of the metrics that we require to output our data in the desired output. First we need to combine each of the branches so they are in a single table. We can do this using the Union as the tables have the same structure so we want to 'stack' them on top of each other.
After the union, we want to change to shape of the table again. This time we want to pivot using a Rows to Columns pivot, so that each of our Years is a separate field with the Max Value for each metric:
After this pivot we are ready to output our data in the desired format. It should look like this:
