2024: Week 10 - Solution
Solution by Tom Prowse and you can download the workflow here.
The challenge this week is focussing on Tableau's new release of Tableau Pulse. Tableau Pulse is great for empowering employees to make better, faster decisions by tracking a metric's current value and comparing how it's changed. Unfortunately, this doesn't happen by magic and the underlying data needs to be sorted to display these metrics effectively... that's where Tableau Prep comes in!
Step 1 - Dates
Tableau Pulse works best when we have our data at a daily or weekly level so we can first ensure that our transaction data is structured in the most effective way.
First we want to only look at the last 2 years of data and in this case we want years 2023 & 2024. We need to ensure that our Transaction Date is a date field (not a string) then we can filter to keep only the years 2023 & 2024. There are multiple ways of filtering for this, but in a real life scenario you'll want this to be updating automatically, therefore we are going to use the relative date filter to return the last 2 years.
After filtering for these two years we can then add some additional rows to make sure we are covering the days when the store is closed. For this we can utilise the 'New Rows' step to fill in the missing days in the Transaction Date between the Min & Max within the data source. As the store was closed on these days we can show the values as Null.
Our table now looks like this:
Step 2 - Products
Next we want to focus on the cleaning the product fields so that we can join this with the Product Table. The Product ID field includes information about the product scent, the size, and the type so we need to split these out into separate fields using a custom split on '-' to split all values.
As a result we have the three new fields that we can rename accordingly
We can then tidy up the Scent field by replacing the '_' with a ' ' (space):
Produce Scent
REPLACE([Product Scent],'_',' ')
Finally we can make sure that the payment codes for Cash or Card are presented in an easy to understand manner. For this we can change the field type to a string and then replace 1 with Card and 2 with Cash using the Profile Pane.
Our table is almost ready to join with the Product table input, but first we need to make sure that the Product Size field is the same in each. Within the Product Table input we need to ensure there are no Null values so we can merge the Pack Size and Product Size together to get a single field with no Nulls. You can do this by selecting both fields then selecting Merge Fields.
At this stage we are now ready to join our tables together by using a left (or right) join where we include all of the rows from the Transactions workflow using the Product Scent, Product Type, and Produce Size fields.
After the join the table should look like this:
Then finally we can calculate the Quantity for each transaction
Quantity
[Sales_Before_Discount]
/
[Selling_Price]
Step 3 - Loyalty
The next stage involves us combining the Loyalty table into our analysis. For this we need to tidy up the table first before it is ready to join with our original workflow.
First we want to make sure the Customer Name field is formatted correctly (First Name, Last Name). To do this we can split the Customer Name field using the ',' so we have two fields - one for first name, one for last name.
We can then bring these back together using a string calculation:
Customer Name
PROPER(
[First Name]+" "+[Last Name]
)
Utilising the 'Proper' function here allows us to get the correct capitalisation as well.
Next we want to ensure that the Tier field has 3 tiers and they are spelt correctly. Using Tableau Prep's grouping functionality, we can group these by 'Spelling' then make sure the spelling is correct for each.
Then finally, the loyalty discount field needs to be a number instead of having the % included. For this we can remove all punctuation using the Clean functionality. Then use a calculated field to make it a decimal value:
Loyalty Discount
[Loyalty_Discount]/100
At this stage the Loyalty table should look like this:
Then we can join this table to our original workflow using a left (or right) join that includes all of the Transactions where the Loyalty Numbers match on both sides.
Step 4 - Profit
The final step is to calculate the Profit. For this we first need to calculate the sales after all of the discounts have been applied:
Sales_After_Discount
[Sales_Before_Discount]*(1-[Loyalty Discount])
Then from here we can calculate the Profit:
Profit
ROUND(
[Sales_After_Discount]-([Unit_Cost]*[Quantity])
,2)
Then clean the field names so that they are in an easy to read format that doesn't include the '_'s. For this we can use the 'Rename Fields' functionality that allows us to remove all of the '_' from the fields at the same time.
We are now ready to output our table that should look something like this:
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!
