Preppin' Data

Solution by Tom Prowse and you can download the workflow here . Step 1 - Clean Actuals & Budgets The first step is to input the Actual and the Budget tables so that they are separate branches in our workflow. We want to combine these branches but first we need to clean each table so that they join nicely.  Actual   For this branch we just need to make a change to the 'TransactionFees' value in the 2022 field and make sure there is a space between the two words - 'Transaction Fees'. Budget For this branch we need to make a few changes to the Budget field so that the numbers are all formatted correctly as a number instead of a string. To do this we can duplicate the Budget field, then remove all letters from this field using the Clean functionalities in Tableau Prep  From here we can change this to be a whole number and then use the following calculation to format the budget numbers correctly:  Budget   IF ENDSWITH([Budget],'K') THEN [Budget-1]*1000 ELSE [Budg

Challenge by: Michał Mioduchowski We're continuing with DS43's challenges so over to Michał to explain the next challenge.  _____________________________________ For this week, Superbytes has requested us to look at their budget sheet for 2022. The company believes that their rough budget estimates were very close to the final budget allocations for that year. Examining historical budget data allows the company to identify trends in spending patterns over time. This can provide valuable insights into how expenses have evolved and help predict future expenditure trends. CEO Phil Down would like us to find the exact areas of mismatch between predicted and actual spending. Inputs There are 2 inputs for this challenge: Forecasted Spending   Actual Spending Requirements Input the Excel file Match Sheet 1 and Sheet 2 in formatting. Both should include: Category field [String] Budget/Actual field [Number (Decimal)] Join both sheets based on Category field to create a single table wit

Solution by Tom Prowse and you can download the workflow here . Step 1 - Days to Removal First we want to combine both data sets by using an inner join on Product ID:  This then allows us to calculate the number of days that it took to remove the products. We can calculate this using a datediff calculation:  Days to Removal   DATEDIFF('day', #2025-05-13#, [Date]) As a result our table should look like this:  Step 2 - Recall Targets Next we want to categories the products to determine whether or not they had met the target. Using an IF calculation we can give each one a category:  Category IF [Days to Removal]<=7 THEN 'On Target' ELSEIF ISNULL([Days to Removal]) THEN 'Incomplete' ELSE 'Overdue' END This allows us to give each of the products a category based on whether they have been removed on time or not. Step 3 - Output 1 - Store Rank We can now start to create our outputs. The first one is where we rank our stores based on how long it has taken the

Challenge by: Hewan Zewdu We're continuing with DS43's challenges so over to Hewan to explain the her next challenge.  _____________________________________ For this week’s Preppin Data challenge, we’ll continue looking at stock/ store data where some items had to be recalled from the shelves. This time we will be determining how long it has taken to remove those items, and whether or not they have been removed at all. All stores were told the items in the ‘Recalled Items’ table needed to be removed from shelves on 13/05/2025. They were given 1 week to recall all the items (i.e. this must have been completed by 20/05/2025). Inputs There is 1 input file with two sheets which is identical to the previous challenge, but now containing a date field, which is the date the items were removed from the shelves. You can download the output from here . Requirements Input the data Calculate how long it has taken for each item to be removed, based off the day the recall was announced (13/

Solution by Tom Prowse and you can download the workflow here . Step 1 - Recalled Items The first step is to identify which items have been recalled, these can be found in the Recalled Items table but we need to identify these at a store level.  To do this we can use an inner join on product ID between the two tables:  This acts like a filter and only returns the items that have been recalled. After removing the duplicate fields the table should look like this:  Step 2 - Total Losses Next we can calculate the total losses as a result of the recalled items.  For this we need to calculate the total price of the items recalled. We calculate this by multiplying the Unit Price by the Quantity.  Total Price   [Unit Price] * [Quantity] We can then use this to calculate the total losses split by Category and also by Store. For this we'll need two separate aggregation tools on different branches.  Per Category Per Store   We can then make sure the Total Price field is rounded to 2 decimal p

Challenge by: Hewan Zewdu For April, one of The Data School cohorts (DS43) are taking over #PreppinData with a set of challenges all based on our new (fake) company SuperBytes - everyone's favourite Supermarket! Over to Hewan for the first challenge of the month.  _____________________________________ This week at SuperBytes there has been a manufacturing issue, and as a result a number of items must be recalled from the shelves! Input There is 1 input Excel file with two sheets. You can download the input from here . Requirements  Input the data tables Identify which recalled items are located in each store Create a field which shows how much money will be lost for each item Then calculate how much money will be lost for each category, and round it to 2 dp Output this first dataset Phill Down (the CEO of SuperBytes) now needs to know how much money each store has lost! Find those values for each store, again rounding to 2 dp. Mr Down has decided that any store that has lost £500

Solution by Tom Prowse and you can download the workflow here .  Step 1 - Input Data The first step is to ensure that we have all of the data that we require from the multiple different sheets within the input. As they all have a similar structure we can use the union multiple tables functionality to input these as a single input. We want to include all of the sheets within the input: Within our input the File Paths field can become our Year field and the table should look like this:  Step 2 - Easter Week Number Now we have all of the data in our workflow we can start to calculate the week number that Easter occurs in each year. To do this we want to duplicate the Sales Date field, and then convert this to a Week Number using the Convert Dates functionality. This will then become our Week Number field.  Then to find the Easter Week Number we can rank these weeks to get the 12 previous weeks that the Easter products have been released. We need to group by Year and use a dense rank based