2024: Week 19 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Input Tables
The first step is to input all of the tables from the input file. We want to create a union and stack them on top of each other so therefore we can utilise the 'Union multiple tables' functionality within the input step. From here we want to select all of the tables where the file name matches 'SuperBytes Sales_Profits.xlsx' and don't need to work about any wildcard values.
As a result we should have the worksheets from various years input into the workflow:
Once the tables are in the workflow, we can rename some of the fields so they match the data that is in them:
F1 --> Quarter
Table Names --> Year
Then make sure the Year is a whole number. At this stage the table should look like this:
Step 2 - Sales & Profit Values
We want to ensure that the number values in the Sales & Profits field are formatted in the same way and take into account the string value.
To do this we want to isolate the K or M from the rest of the number. Tableau Prep has some handy features that allows us to do this without having to write any calculated fields.
We first need to duplicate both of the Sales & Profits fields. Then for both of them we can remove any Numbers and Punctuation then add a 'Unit' to the end of each field name so we have Sales Unit & Profit Unit.
We then want to remove all letters from the original Sales & Profit fields so that we just have the number values.
Now we have the number and unit separated we can use both of these in a calculated field to show the full number based on the unit:
Sales
IF [Sales Unit]='K'
THEN [Sales]*1000
ELSEIF [Sales Unit]='M'
THEN [Sales]*1000000
END
Profits
IF [Profits Unit]='K'
THEN [Profits]*1000
ELSEIF [Profits Unit]='M'
THEN [Profits]*1000000
END
At this stage our table should look like this:
Step 3 - Total Sales & Profits per Year
The final step is to calculate the total sales and profits for each year. We can do this by using an aggregation step where we group by year and then sum the sales and profit fields:
We then have our final output 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!
