2024: Week 45 - Solution
Video Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Combine Tables
First we want to combine all of our input tables together into a single table. We can do this using two inner joins, first joining the Available Stock and the Product tables using the Product ID:
Then join this table to the Supplier table using Supplier = Supplier ID:
After the joins we can remove any additional fields and our table should look like this:
Step 2 - Orders & Quantity
Now we have the inputs in a single table we can calculate the Stock and Quantity levels. First we can calculate whether stock has been ordered or not:
Stock Ordered
[Quantity Available]<=10
Then we can calculate the quantity ordered:
Quantity Ordered
IF [Stock Ordered]
THEN 30-[Quantity Available]
END
From here we can now calculate the quantity per week using the following calculations:
Previous Week Quantity Available
{ PARTITION [Store],[Product] : { ORDERBY [Inventory Date] asc : LOOKUP([Quantity Available],-1)}}
Previous Week Quantity Ordered
{ PARTITION [Store],[Product] : { ORDERBY [Inventory Date] asc : LOOKUP([Quantity Ordered],-1)}}
Then we can calculate the Quantity Sold:
Quantity Sold
IF ISNULL([Previous Week Quantity Available]) AND ISNULL([Previous Week Quantity Ordered])
THEN NULL
ELSE
ZN([Previous Week Quantity Available])
+ ZN([Previous Week Quantity Ordered])
- [Quantity Available]
END
We can then remove the two previous week calculations and calculate the Total Orders:
Total Orders
IF [Stock Ordered] THEN 1 ELSE 0 END
Our table now looks like this after these calculations:
Step 3 - Averages
The final part of the task is to calculate the averages, so first we need to total the Orders & Quantity using an aggregation step where we group by Store, Category, & Product and Sum the Total Orders, Quantity Sold, and Number of Rows fields:
We can then calculate the following:
Avg Quantity Sold per Week
ROUND(
[Quantity Sold]/[Number of Rows (Aggregated)]
,1)
Avg Order Frequency
ROUND(
[Number of Rows (Aggregated)]
/
[Total Orders]
,1)
We can then rename the Quantity Sold to Total Quantity Sold and remove any additional fields. We are then ready to output the final table:
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!
