For this week’s Data School project, I chose to complete a Preppin’ Data challenge. Since I had focused primarily on Alteryx over the last few weeks, I felt compelled to use this opportunity to reimmerse myself back into the wonders of Tableau Prep.
This challenge was centered around the idea of helping a company set out future aims based on their quarterly sales on a store-by-store basis. The input file had three sheets: 1) 1 year’s worth of monthly sales data per store. 2) a list of quarterly sales targets per store. 3) A set of ‘next steps’ based on the success to which targets were either met or unmet.
Here is a list of the requirements:
- Input the data
- Remove any non-store sales data
- Form a date to help with your quarterly calculations
- Determine Store Quarterly Sales
- Join on Store Quarterly Targets
- Determine Store variance to Target – actual as well as percentage difference
- Match Action Description to Variance %
- Output the data
My Approach
I knew that eventually I would have to join the monthly sales sheet with the quarterly target sheet, so in order to do this, the monthly sales data would need to be converted into quarterly sales data. Firstly, I began by cleaning the monthly sales data by removing any non-store sales in the dataset, so I simply removed the ‘total sales’ row from the data. Since this sheet has several columns for each month of sales data, this needed to be pivoted. I did this using a columns to row pivot with a wildcard match of the word ‘Month’. This is how the pivot should look:
Now we are in a position to convert the monthly sales into quarterly sales. There is more than one way of reaching this step in Tableau Prep, but for the purposes of this challenge, I opted to utilise the ‘Group Values’ feature on a ‘clean step’ by adding each three months into each respective quarter. This step can be seen below:
Despite grouping the months into quarters, our sales data is still aggregated on a monthly basis. By grouping by store and quarter and summing the monthly sales data in an ‘aggregate step’, Tableau Prep will provide us with sales data that is now aggregated at the quarterly level. So our data will now look something like this:
Our quarterly sales data for each store is now ready for a join, but our quarterly targets sheet is not quite at that point. All that is required to amend this, is to do a columns to rows pivot on the quarterly targets, again using a wildcard match on the letter ‘Q’. Now these two sheets have been perfectly configured, we are now able to join them on the ‘store’ and ‘quarter’ columns.
The next part of the challenge requirement is to determine the store variance to target (actual and percentage). This can be done using two simple calculated fields:
Variance to Target = (Sales) – (Sales Target)
Variance to Target % = ROUND((Sales) / (Sales Target) * 100)
These two calculated fields relate to the final sheet of the input regarding the targets and the corresponding next steps for the future. This sheet has a ‘Range’ column of target percentages, but we need to split these percentages into a minimum and maximum value so our ‘Variance to Target %’ column in the other sheet can fall into one of these ranges. Most people would use REGEX in this situation, but since I am very new and inexperienced to that method, I opted for a series of calculated fields, split and data cleansing techniques on a ‘clean step’ to remove any non-alphanumerical symbols from the newly-formed ‘min’ and ‘max’ columns. Here are the changes I made in this step:
Finally we are able to add the final piece to this Preppin Data puzzle, by joining the target steps sheet to the main sheet using the following join:
Variance to Target % >= Min
Variance to Target % <= Max
After this, all that is left to do is rename and reorder the columns, as well as removing any unwanted fields. This should provide you with a dataset which is now ready for outputting.
