2020: Week 42 - Solution

 

Solution by Tom Prowse and you can download the full solution here. 

This week's challenge was all around calculating the Year/Month/Week to Date calculations which are used throughout various different companies to give an indication about how they are performing. Instead of just basing these calculations on a single date, we wanted to take this a step further and compare the Week and Day number so that we are comparing the same periods throughout the year.

This may not be the most simple way to solve it, but I wanted to try and make it as future proofed as possible! For some alternative solutions take a look at the Preppin Data Tableau Forums to see some other community members workflows.

Step 1 - Create Daily Targets Table

Our targets table is currently only a weekly breakdown so we want to use a daily scaffold to help build this out so we have a target for each day. 

The first step is to bring in the Transactions table, which we will use for the daily scaffold. Within this table we are given each individual day, therefore we can use the native Tableau Prep feature to create the Year & Week fields.

We are going to repeat this process for Year and Month so that we have a conversion for each day. Remember to duplicate the date field so we still have the daily breakdown!

Next we want to make sure there is only one row per day. Therefore, we use an aggregate tool to Group By Week, Year, & Transaction Date: 

Now we are sure that we have one row per date, we can use this as the scaffold to create the daily targets table. 

After bringing the WeeklyTargets table into the workflow, we want to divide both the Quantity and Income by 7 to get a daily target: 

Daily Quantity

[Quantity Target]/7

Daily Income

[Income Target]/7

We also want to create a field that will allow us to identify this is a target when joining. We can just create a field called Period with the string 'Target'.

Now we're ready to join the Scaffold and Targets tables together. The join condition for this is: 

Our workflow looks like this: 

We now have a table that looks like this: 

Step 2 - Add Periods

Next we want to classify which period our transactions occurred in - was it This Year, Last Year or Other. To do this we need to find out what the latest date is within our Transactions table. 

Using a separate branch, from the step where we created our 'date parts', we can create a Fixed LOD to find out what the latest year is: 

Latest Year

Now we use this field to calculate which period our transactions occurred in: 

Period

IF [Latest Year] = [Year] THEN 'This Year' 

ELSEIF [Latest Year]-1 = [Year] THEN 'Last Year'

ELSE 'Other'

END

Step 3 - Latest Week/Day Combination

Continuing with the Transactions part of the workflow, we now want to identify what the latest Week/Day combination is. To do this we first need to find out what the latest week is with the following steps: 

1. Filter Period - Keep only 'This Year'

2. Calculate the Latest Week

Latest Week

3. Calculate how many days in each week

Days in Latest Week

4. Filter where Week = Latest Week

We now have a field which tells us what the latest week and how many days are in this week from This Year:

We now use an aggregation tool to summarise this so we can use it again in other parts of the our workflow. The aggregation setup looks like this: 

Now we have this as a single row, we can append it to both of our other tables. 

First we want to calculate which day of the week each of the transactions have occurred on a separate branch from the 'Period' step. We can calculate this using a Datepart calculation: 

Day in Week

DATEPART('weekday',[TransactionDate])

Our full workflow now looks like this: 

We're ready to append the Latest Week/Day to our tables. First, we need to create a join calculation which will be simply a new field called Join with the string 'Join'. This can be anything, as long as it's the same on each branch, because we want all of our rows joining together. We want to create this on all three branches so far.

We can then join the Transactions with the Latest Week/Date: 

Then also the Targets to the Latest Week/Date: 

We now have two branches and the workflow looks like this: 

Step 4 - Combine Tables & Filters Dates

Next, we want to combine each of the Transactions and Target branches. These have a similar structure which is ideal, however we first need to create a Days in the Week field on the Targets workflow: 

Day in Week

We can then use the Union tool to stack both of the tables on top of each other so our data should look like this: 

Now we have a tables combined, we want to filter out some values so that it makes it easier to calculate the YTD, MTD, & WTD. 

We want to remove any rows which are passed the latest Week/Day combination for the Target and Last Year rows. To do this we use the following calculation within a filter: 

[Week] <= [Latest Week] 

AND 

(IF [Week] = [Latest Week] 

    THEN [Day in Week]<=[Days in Latest Week] 

    ELSE TRUE

END)

Step 5 - Calculate YTD, MTD, WTD

We're now at a stage where we can focus on calculating our totals for each of the YTD, MTD, WTD metrics. I have decided to split this into three different branches to make everything nice and clear:

YTD

We have done a lot of the hard work here by removing the extra dates from the workflow. Therefore, we want to use an aggregation tool to find the total for each product and period: 

 We can then create a string field of 'YTD' which will act as a label. 

MTD

This is a little more complicated as we haven't converted any months. But we will follow the same process as we did for Year/Week:

1. Convert Transaction date to Month

2. Find the Latest Month using a LOD:

3. Use a calculation filter to keep only the latest month where Month = Latest Month. 

This has now limited our data set to the latest month, which will also be our MTD for each period as we have already filter out the extra days from the previous periods. 

Therefore we can use an aggregation to calculate the totals and add a 'MTD' label, like we did for the YTD: 

WTD

Finally we want to calculate the Week to Date for Quantity and Income. First we need to filter the data so that it just shows the latest week. We can use a calculation filter for this where Week = Latest Week. 

Again, we are going to repeat the process and use an aggregation with the same setup, and create a 'WTD' label: 

Now we have the totals for each we need to combine these into a single table. To do this we use a Union tool by connecting all three. Remember, you can add an additional branch by connecting to 'Add' after the initial union.

Step 6 - Calculate % Difference

The final task this week is to calculate the % difference between This Year and Last Year, and also This Year and Target for each product. 

In order to make this easier, first we want to pivot the data so we have a row for both Income & Quantity: 

We now have both of our metrics in a single column, therefore this is a good point to round the values to 0dp using this calculation: 

Pivot1 Values 

Round([Pivot1 Values],0)

After rounding, we want to pivot the result back so we have column for each period:

Finally we can calculate the % difference with the following calculations: 

% Difference to Target

ROUND(

(([This Year]-[Target])

/

[Target])

,2)

% Difference to Last Year

ROUND(

(([This Year] - [Last Year])

/ 

[Last Year])

,2)

After this we can now output the data which should look like this: 

The full output can be downloaded here. 

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, @JonathanAllenby & @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!