2021: Week 21 - Solution
Solution by Tom Prowse and you can download our workflow here.
This was our final challenge for the month of May and our calculations month! We looked at level of detail, ranks and using some of the other calculations that we have touched on throughout the month.
The challenge was to analyse what products are selling from Prep Air's on-flight trolley at a higher amount than before and to see what the top 3 products are, based on their price rise.
Step 1 - Input Data
The first step is to input our data. We only have a single data source, but there are multiple tables within this file, therefore instead of bringing in each of these individually we are going to use the wildcard input.
To do this, bring in one of the tables then select 'Multiple Files' from the input tool. Next select 'Wildcard Union' and then we want to bring back all files that start with the word 'Month'.
Step 2 - Create Date
Now we have all of the tables in a single input, we can start to create the purchase date field.
First we need to extract the number from each of the rows in our 'Table Names' field. We can do this by 'Removing Letters' from the clean functionality and then changing the data type to a 'Whole Number'. We now have the numerical value for each month.
Next, we combined this with the 'Day of Month' field using the MakeDate function:
Date
MAKEDATE(2021,[Month], [Day of Month])
Then the final step is to identify the products that have been purchased on or before the 1st June. To do this we can use the following calculation:
New Trolley Inventory?
MONTH([Date])>=6
This calculation identifies that June is the 6th month so will return a true or false depending on the date being before or after 1st June.
After doing some tiding, our table should look like this:
Step 3 - Clean Product Field
Within the product field we want to remove any of the detail after the hyphen or return the full name if there is no hyphen. Therefore, we want to use the custom split functionality to return everything before the first hyphen:
This will create our new product field with just the right amount of detail that we were looking for!
Step 4 - Price Calculations
The final area of calculations is based on the price of the products.
First, we need to make the price field usable in numeric calculations by changing this from a string to a numeric field. However, we can't just change to a decimal number as this will return nulls, therefore we have used the Mid function to extract only the numbers:
Price
MID([Price],2)
This removes the $ symbol, by returning everything from the 2nd character onwards, then we can change the field to be a decimal number.
Now we calculate the average selling price for each product. To do this we want to use a Fixed LOD calculation using the visual analytical calculation in Tableau Prep:
Here we have grouped by Product, as we want to find the average price 'for each' product.
The next calculation is to see what the variance is between the selling price and the average price for that product.
Variance
[Price]-[Avg Price per Product]
Then can rank the variances with 1 being the highest positive variances from the avg. Similar to the LOD, we are going to use the visual analytical calculation to calculate the rank by grouping by Destination & New Trolley Inventory then ranking the Variance in a descending order:
Finally, we only want to see the top 5 for each of the ranks, therefore we can filter our data using a range with a Max 5 on the rank field:
Here's our full output for comparison.
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!
