2019: Week 43 Solution
We’re getting closer to the end of the year so it’s about time to do some end of year appraisals and encourage our stores to set some targets for next year. Automating this would be ideal for us, so that’s the challenge for us this week.
First up is cleaning the Monthly Sales file so we can remove any fields that don’t contain store information, in case we remove the ‘Total’ record from the Store field. The next step, is to pivot the data so that all of the monthly totals are in one column. The pivot should look like this:
After pivoting the data, we can then rename the newly created fields so that they are more recognisable, we’ve called the new fields Month Year and Sales Value. After the pivot, we have multiple rows of data for each month, and we can use Tableau Prep’s grouping functionality to group the specific months in quarters. For example, Month 1, Month 2, Month 3 would be grouped together and renamed as Q1.
This technique works for this solution, however may not be the best going forwards when more months come into play. Therefore, an alternative could be to use a calculated field which picks up the month number instead of manually grouping them.
After we have grouped the months into a quarter, we can use the aggregation tool to find the total sales value for each store in each quarter. The aggregation tool would look like this:
After we have calculated the total sales for each store, we can start to prepare the Store Quarterly Targets sheet. As we have prepared the Store Sales to a quarterly level, we need to do the same here so that we can join the tables at a later stage. Therefore, the first step is to pivot the Quarter totals so that they are in one column. The pivot setup should look like this:
After renaming the newly created fields, we are now ready to join the Sales and Store Target tables together. We can do this by using an inner join on Quarter, and Store/Location. The join should be set up like this:
Now we have joined these tables, we can do some further cleaning by removing any duplicated fields, and also removing any letters from the Quarter field. To do this, we can use Tableau Prep’s clean functionality ‘Remove Letters’.
Next we need to calculate the variance to target for each of the stores. Variance is calculated by subtracting the Target Value from the Sales Value:
Variance to Target
[Sales Value]-[Target Value]
We can then calculate this as a percentage:
Variance to Target %
Round(([Sales Value]/[Target Value])*100,0)
Now we have calculated the variance, we can turn our attention to the final join needed where we bring together our Sales/Targets table with the Targets - Next Steps. In order to prepare the Targets - Next Steps sheet, we need to split the Range field into a Range From and Range To. To do this we used the automatic split functionality, which split most of the numbers that we required into the two new columns.
To complete the split, and remove the nulls, we have used a couple of Regex expressions to populate the new fields. These are as follows:
Range From
IFNULL(
REGEXP_EXTRACT([Range],'(^\d+)')
,'0')
This calculation fills any null values with any digit which is at the start of the string, else it fills it with a 0.
Range To
IFNULL(
REGEXP_EXTRACT([Range],'(\d+)%$')
,'9999')
This calculation fills any null values with any digit which is before a % sign and at the end of the string, else it fills it with a 9999. This 9999 is just a really large number to represent the ‘+’.
Note, before completing these calculations, you need to change the data type to a string in order for the Regex expression to work correctly. You can then change the data types back to a number at the end of the step.
We are ready to join the two tables together, with the following join conditions:
Variance to Target >= Range From
Variance to Target <= Range To
This will allow each store to have the correct action string depending on what variance % the store had. The final step is to just rename and remove any unwanted fields, then we are ready to output the data.
