2021: Week 4 - Solution
Solution by Tom Prowse and you can download our full workflow here.
This week was our final fundamentals challenge in January, so hopefully you have come a long way and are starting to understand what Tableau Prep is all about!
For the challenge this week we focused on combining a few of the different techniques that we have covered in the previous week. This included joins, pivots and calculations, whilst we also added some new analytical calculations in the form of a Rank.
Let's take a look how we solved it!
Step 1 - Wildcard Union Data
The first step is to bring all of our sales data in for each store by using the wildcard union within the input tool. This is the same technique that we used last week, however this time we want to exclude the Targets sheet. So our input looks like this:
Notice how the targets table is no longer in the 'Included Sheets' column.
After the input we can clean up a couple fields by removing 'File Paths' and then renaming 'Table Names' to 'Store'.
Step 2 - Pivot & Aggregate Products
We now want to take all of the product fields, then pivot these into a single column. We can do this by using the Columns to Rows pivot then dragging all of the fields into the Pivoted Fields section.
As a result of the pivot we can then split the pivot names field into our Customer Type and Product fields. An automatic split should work here, or alternatively you can use the custom split on '-'.
After renaming the pivoted fields to Customer Type, Product and Product Sales, the next step is to convert the date into the corresponding quarter field. We can do this using the native Tableau Prep 'Convert Dates' feature and then rename it 'Quarter'.
The final step of this stage is to use an aggregation tool to calculate the total sales for each store and quarter combination. We want to Group By Quarter & Store then Sum Products Sold.
As a result of these changes our data should now look like this:
Step 3 - Join Targets
Now we want to combine the store sales with their quarterly targets. First, we need to bring the targets sheet into our workflow by dragging the table in from the blue menu on left-hand side.
Once we have the Targets sheet, we can then join this to our existing workflow using the following join conditions:
As a result of the join, we have 20 rows with the Quarter, Product, Sales and Target (we have removed any other fields):
Step 4 - Calculate Variance & Ranks
The final step this week is to calculate the variance between the sales and target values using the following calculation:
Variance to Target
[Products Sold] - [Target]
As a result we now have an additional column for the variance:
Finally we want to rank each of the store depending on their variance for each quarter. Tableau Prep have made creating ranks really simple by introducing the analytical calculation visual editor last year. We can simply select the three dots on the Variance field, then 'Create Calculated Field' then 'Rank'
Once selected, Tableau Prep then opens the analytical calculation visual editor where we can set the conditions for our rank.
Within the setup we want to Group By Quarter (for each Quarter), then Order By Variance to Target Descending.
After the rank we then have our final output that looks like this:
You can download the full output here to compare your answers.
Thank you for taking part in our fundamental challenges this month, we've loved seeing all of the different solutions and everyone getting involved! Hopefully we have given you a starting step on your Tableau Prep journey and we hope to see you for the challenges throughout the year!
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!
