2022: Week 13 - Solution
Solution by Tom Prowse and you can download the workflow here.
This week we take a first look at one of Tableau Prep's newer features, Parameters! We used parameters to recreate the classic use case of a Pareto chart to see how many customers make up a certain percentage of our sales.
Step 1 - Total Sales per Customer
After inputting our data, the first step that we want to do this week is to calculate the total sales for each customer. As some customers have multiple rows of data, we need to use an aggregation tool to total these up. The setup for the Aggregation step is: Group by Customer ID, First Name, Last Name, then Sum Sales:
As a result we should now have a single row for each customer and their total sales:
Step 2 - % of Total Sales
Next we want to calculate how much each customer represents in the total sales. First, we want to calculate the total sales across all customers by using a Fixed LOD:
Total Sales
We can then use this to calculate the % of total for each customer:
% of Total
[Sales]*100
/
[Total Sales]
Our table should now look like this:
Step 3 - Calculate Running Total
The next step is to calculate a running total based on the % of total calculation that we have just calculated. We can calculate a running total with the following calculation:
Running % Total Sales
{ ORDERBY [% of Total] desc : window_sum([% of Total])}
Then finally we can round this running sum to 2 decimal places:
Running % Total Sales
ROUND([Running % Total Sales],2)
Then finally we need to calculate the total number of customers that we have in the data set using the following calculation:
Total Customers
Step 4 - Create Parameter
If parameters are new to you then make sure you check out this blog post on how to create parameters in Tableau Prep.
To create a parameter we need to select the parameter dropdown from the top menu:
The Current Value doesn't matter too much at this stage, as this is going to be the bit that we can change every time we run the workflow.
Now that we have created the parameter, we need to include it somewhere in our workflow or else it won't impact any of the changes.
For our use case, we want to use the parameter within a filter so that we only return values that are less than or equal to our Running % of Total field. Therefore, we can add it into a filter calculation like this:
Notice how the parameter is displayed in a purple colour, just like in Tableau Desktop!
Step 5 - Raw Data
We can now start to split our workflow into separate branches to create our two desired outputs. First, we want to output the raw data including the parameter value in the name of the output.
First we want to remove the Total Customers field.
Then we can create an output with the following setup
The output should look like this:
Step 6 - String Data
Finally, the 2nd branch is where we want to write the outcome in an English string. First we need to calculate the number of Customer IDs and return the Total Customers that we calculated earlier. For this we only need a single row, therefore we can use the aggregation tool to calculate this.
If we group by Total Customers, this will return a single row as the number is repeated across all rows. Then we want to do a distinct count based on the Customer ID, to return how many customers remain.
Next we can calculate the % Total Customers using this calculation:
% Total Customers
ROUND(
[Customer ID]*100
/
[Total Customers]
,0)
Then finally we can use the parameter value within a string calculation to create our outcome explanation string:
Outcome
STR([% Total Customers]) +"% of Customers account for "+STR([Sales Percent])+"% of Sales"
Again the parameter is highlighted in purple, and the final output should look something like this:
You can download the full outputs here. These outputs are based on a parameter value of 80.
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!
