2024: Week 21 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Loyalty Points
After inputting the data, we can first calculate the loyalty points for each customer:
Loyalty Points
ROUND([Sale Total]/50,1)
Then we can categorise the customers based on the number of points they have:
Category
IF [Loyalty Points]>=7
THEN "MegaByte"
ELSEIF [Loyalty Points]>=5
THEN "Byte"
ELSE "No Byte"
END
At this stage our table has two new columns that look like this:
Step 2 - Aggregate & Pivot
Next we want to total the number of customers who qualify for each category by using an aggregation step:
Then once we have aggregated we need to pivot the data so that Male & Female genders have a separate column. For this we want to use a Rows to Columns pivot:
The final task is to calculate the % of Total for Male & Female.
First we need to calculate the total of each by adding both the Male and Female totals together:
Total
[Male]+[Female]
Then we can calculate the % of total for each gender:
Female
ROUND([Female]*100/[Total],1)
Male
ROUND([Male]*100/[Total],1)
We can then output our data that looks 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!
