2024: Week 17 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Month Spending Input
First we need to input the Monthly Spending table so that we have all of the months in a single table. To do this we can utilise the 'Union multiple tables' option within the input step. We want to include all tables that have the word 'Month' in the title and include the wildcard symbol afterwards so it picks up multiple months.
Once we have all the tables in a single input, we can then clean this table by removing all of the fields with a month-year title (eg, Apr-23, Aug-23 etc) and rename TransactionFees to Transaction Fees.
Next, we want to utilise the Table Names field to extract the month name for each of the rows. To do this we can split off the last occurrence after the 'Month-' text:
After removing and renaming some fields our table should look like this:
Step 2 - Budget Input
Next we can input the Budget table. For this table we want to identify the Budget amount and the area that it is allocated to. To extract the Budget amount we can use a split to remove the text after the number value:
Then rename the split field to budget so the table looks like this:
Step 3 - Combine Tables
We now need to ensure that both tables are in a similar format so that we can join them together. The budget table is ready, but we need to make some changes to the Monthly branch.
First we need to pivot the table using a Columns to Rows pivot so that each of the months is in a singe column and we have created a long and thin table.
We can then calculate the total for each category by using an aggregation step where we group by category and then Sum the Actual Spending:
Now both branches are in a position where we can join them together. For this we want to use an inner join where Category = 2023:
As a result of the join the table should look like this:
Step 4 - Actual vs Budget
The last step is to calculate the difference between the Actual and Budgeted values. For this we need to filter the table so that we are focussing on only the categories where the Actuals were greater than the Budget - [Actual Spending]>[Budget]
Then we can calculate the monthly budget by dividing by 12:
Budget
ROUND([Budget]/12)
Then we can add back in the original Actual values from the Month branch before the aggregation step by using a join. Before the join we should round the values for actual spending in a separate step:
Actual Spending
ROUND([Actual Spending])
Then we can join both branches using an inner join on Category:
Then the last step is to calculate the difference between the Actuals and Budget amount:
Difference
[Actual Spending]-[Budget]
Now we can rank the values to find the category with the highest overspend in each month:
Rank
Then finally we can filter the table to keep only the 1st ranked category in each month.
Our output should look 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!
