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! 

Popular posts from this blog

2024: Week 1 - Prep Air's Flow Card

Image
Created by: Carl Allchin Welcome to a New Year of Preppin' Data challenges. For anyone new to the challenges then let us give you an overview how the weekly challenge works.  Each Wednesday the Preppin' crew (Jenny, myself or a guest contributor) drop a data set(s) that requires some reshaping and/or cleaning to get it ready for analysis.  You can use any tool or language you want to do the reshaping (we build the challenges in Tableau Prep but love seeing different tools being learnt / tried). Share your solution on LinkedIn, Twitter/X, GitHub or the Tableau Forums Fill out our tracker so you can monitor your progress and involvement The following Tuesday we will post a written solution in Tableau Prep (thanks Tom) and a video walkthrough too (thanks Jenny) As with each January for the last few years, we'll set a number of challenges aimed at beginners. This is a great way to learn a number of fundamental data preparation skills or a chance to learn a new tool — New Year...
Read more

2023: Week 1 The Data Source Bank

Image
 Created by: Carl Allchin Welcome to a New Year of Preppin' Data. These are weekly exercises to help you learn and develop data preparation skills. We publish the challenges on a Wednesday and share a solution the following Tuesday. You can take the challenges whenever you want and we love to see your solutions. With data preparation, there is never just one way to complete the tasks so sharing your solutions will help others learn too. Share on Twitter, LinkedIn, the Tableau Forums or wherever you want to too. Tag Jenny Martin, Tom Prowse or myself or just use the #PreppinData to share your solutions.  The challenges are designed for learning Tableau Prep but we have a broad community who complete the challenges in R, Python, SQL, DBT, EasyMorph and many other tools. We love seeing people learn new tools so feel free to use whatever tools you want to complete the challenges.  A New Year means we start afresh so January's challenges will be focused on beginners. We will u...
Read more

How to...Handle Free Text

Image
Free isn't always a good thing. In data, Free text is the example to state when proving that statements correct. However, lots of benefit can be gained from understanding data that has been entered in Free Text fields. What do we mean by Free Text? Free Text is the string based data that comes from allowing people to type answers in to systems and forms. The resulting data is normally stored within one column, with one answer per cell. As Free Text means the answer could be anything, this is what you get - absolutely anything. From expletives to slang, the words you will find in the data may be a challenge to interpret but the text is the closest way to collect the voice of your customer / employee. The Free Text field is likely to contain long, rambling sentences that can simply be analysed. If you count these fields, you are likely to have one of each entry each. Therefore, simply counting the entries will not provide anything meaningful to your analysis. The value is in ...
Read more