Preppin’ Data 2020: Week 12 challenge solution

Here is my solution for Peppin’ Data 2020, Week 12. This challenge was about calculating weekly sales figures for products based on total sales numbers.

My solution in Tableau Prep

Original challenge and data set on Preppin’ Data’s blog.

Step 1: Filtering out products that didn’t sell

This challenge had 3 separate tables: ‘Total Sales’, ‘Percentage of Sales’, and a lookup product table. The ‘Total Sales’ table shows weekly sales for all products sold during a particular week, but doesn’t have a weekly breakdown of sales per product per size. This lower level of detail should be calculated using the percentages for each products and sizes sold during a particular week.

'Total Sales' table

'Percentage of Sales' table

Lookup table

The original input for this challenge is one Excel workbook with 3 tabs for each table described above. Once we connect to this Excel workbook in Tableau Prep Builder, we see three separate Input steps in the canvas area.

Let’s start with the ‘Percentage of Sales’ table, and filter out all products that didn’t sell, i.e. the products that made 0% of total sales. We can do it directly in the Input step by clicking Filter values option in the data preview pane, and writing a simple calculation:

[Percentage of Sales]!=0

Step 2: Extracting size information from the lookup table

Next we need to extract the size details from the Product field in the other Input step, a lookup table, to be able to join it later with the ‘Percentage of Sales’ table. Product ID and the size details per product were erroneously concatenated. To extract these details we need to create a new calculated field Size:

IF ENDSWITH([Product], '0.5l') THEN '0.5l'
ELSEIF ENDSWITH([Product],'50g') THEN '50g'
ELSEIF ENDSWITH([Product], '250ml') THEN '250ml'
ELSEIF ENDSWITH([Product],'100g') THEN '100g' END

To complete this step, we need to delete the size details from the Product field by creating a new calculated field called Product:

IF ENDSWITH([Product], '0.5l') THEN REPLACE([Product], '0.5l','')
ELSEIF ENDSWITH([Product], '50g') THEN REPLACE([Product], '50g','')
ELSEIF ENDSWITH([Product], '250ml') THEN REPLACE([Product], '250ml','')
ELSEIF ENDSWITH([Product],'100g') THEN REPLACE([Product], '100g','')
END

Step 3: Joining the ‘Percentage of Sales’ and lookup table

Now that we have common fields in both tables, we need to bring the Join step, and join the ‘Percentage of Sales’ and lookup table on the Product and Product ID fields, as well as on the Size fields. Remove the Product ID and now duplicated Size fields after the join.

Join step settings

Step 4: Creating Year Week Number field

As the final output requires the date to be in the Year Week Number format (for example, ‘202002’), we need to convert the existing Week Commencing field into that format. To do so, we need to find a week number from the Week Commencing field that is currently in the ‘dd/mm/yyyy’ format. There are two ways to go about it, depending on the Tableau Prep Builder’s version you are using.

If you are using the 2020.1.4 version or later, then you can simply click on the three-dot icon in the Week Commencing field’s header, and select Convert Dates > Week Number. That will bring the number of the week as an integer.

New way to quickly convert dates in the 2020.1.4 version

If you are using an earlier version, you can get the same result by writing a calculated field as follows:

DATEPART('week', [Week Commencing])

Now we need to change the data type for the field with the week number (in my case it’s the Week Commencing field) to String, and create a new calculated field called Year Week Number.

As the length of the requited Year Week Number format should be 6 digits, we need to add a zero in front of any week number that is one digit, for example, 2. The calculated field checks whether a week number consists of one digit using the LEN function, and if the condition returns true, appends a zero to the year.

IF LEN([Week Commencing])=1 THEN '20200'+[Week Commencing] ELSE '2020'+[Week Commencing] END

We can remove the Week Commencing and Product fields here as we won’t need them in further calculations.

Step 5: Cleaning the Scent field in the ‘Total Sales’ table

Before we can join the ‘Percentage of Sales’ and ‘Total Sales’ tables, we need to clean the Scent field in the ‘Total Sales’ table as now it has random spaces. To fix this, we need to click on the three-dot icon in the Scent field’s header, and select Clean > Remove all spaces. The only scent which actually has two words in its name is Tea Tree, so we need to create a calculated field Scent to update the existing Scent field:

IF [Scent]='TEATREE' THEN 'TEA TREE'
ELSE [Scent]
END

Remember to change the data type of the Year Week Number field to String as it will be used in the Join step.

Step 6: Joining the ‘Percentage of Sales’ and ‘Total Sales’ tables

Now we can bring a new Join step, and join the ‘Percentage of Sales’ and ‘Total Sales’ tables on the Scent and Year Week Number fields.

Join step settings

We can remove the now duplicated Scent and Year Week Number fields before moving on to the final step.

Step 7: Calculating weekly sales per product per size

To conclude this challenge, we need to add a new Cleaning step and create a calculated field called Sales:

ROUND([Total Scent Sales]*[Percentage of Sales],2)

The ROUND function here rounds the final number to two decimal points.

We need to remove the Total Scent Sales and Percentage of Sales fields, and our final output is ready:

And here is the completed flow for this challenge which you can download from my GitHub repository:

preppin_data tableau_prep data_prep etl

Anya Prosvetova