2020: Week 12 - Solution
You can download our solution here, and watch the video above for a slightly alternative solution.
This week our task is to clear up some messy data in order to return some useful information about the weekly sales of our products.
Step 1 - Clean Dates
The first step is to extract the Year and Week from the date field in the Percentage of Sales table. In the latest release of Tableau Prep, this is now a lot easier as there is a built in function which allows you to extract parts of a date. You can find this by clicking on the '...' on a date field, selecting 'convert dates', then select the conversion that you would like to create.
This is the first step we are going to take, first duplicate the date field twice (one for year, one for week). Then use the date conversion to return the year and week number from each date.
As you can see the week numbers are output as single digits, without the leading '0's, therefore we need to use the following calculation to ensure that it is in the correct format:
Week
IF [Week]<10 THEN '0'+STR([Week])
ELSE STR([Week])
END
We are now ready to combine the week and year together to make the Year Week date field, this can be done with the following calculation:
Year Week Number
STR([Year])+[Week]
We now have a nicely formatted Year Week field:
The final step within this stage is to remove any % of sales that equal 0, as we aren't worried about these products!
Step 2 - Join Lookup Table
We are now ready to join the Lookup table to our Percentage of Sales table. To do this, we need to create a dummy join field on each data set. This can be anything that we like, but we have just created a '1' on each table.
Using this newly created field, we can join both of these tables together on this field:
After this join, you will notice that the number of rows of data has increased massively. This is due to us joining every row from each data table onto each other. Don't worry, we will remove these unwanted rows in a later step!
Step 3 - Parse Product ID & Size
Within the lookup table, the product ID and size fields have been wrongly concatenated therefore we will need to split these out so that we can identify each product. We have chosen to do this using the Replace functionality, however there are other ways you can do this.
We used the following calculations to extract the product and size:
Size_New
We used the following calculations to extract the product and size:
Size_New
REPLACE([Product],[Product ID],'')
Product_New
REPLACE([Product],[Size],'')
Within each of these calculations, we are replacing the product/size with nothing (''), therefore leaving us with the product/size.
Once we have separated the product/size, we can now use the following filter calculation to remove any of the duplicated rows from the earlier join:
[Size] = [Size New]
AND
[Product ID] = [Product New]
We can then tidy the table by removing any unwanted fields.
Step 4 - Join Total Sales
Next up we now want to include the final data table which contains details about the total sales for each product.
Again, some of this is a little bit messy, therefore we need to tidy before we join to our existing flow. We need to make the following changes to the Total Sales table:
The final step before the join is to remove all spaces from the scent field in our original flow. We can now join our flow to the Total Sales table by joining on Scent and Year Week field.
You'll notice that we are now back down to 307 rows, as all of the incorrect rows have been removed.
Again, some of this is a little bit messy, therefore we need to tidy before we join to our existing flow. We need to make the following changes to the Total Sales table:
- Change the 'Year Week' field from a number to a string.
- Use the clean functionality to make the Scent field all lowercase & to remove all spaces.
The final step before the join is to remove all spaces from the scent field in our original flow. We can now join our flow to the Total Sales table by joining on Scent and Year Week field.
You'll notice that we are now back down to 307 rows, as all of the incorrect rows have been removed.
Step 5 - Calculate Sales
The final step of our solution is to calculate each products weekly sales. We can do this by using the following calculation:
Sales
round([Total Scent Sales]*[Percentage of Sales],2)
After this step, the only thing that is left is to remove any unwanted fields to prepare the data for the Output step.
Our output should look like this:
Make sure to fill in the participation tracker, and post you solutions onto our Tableau Forums community page so that we can compare our workflows!
