Quartlery year over year comparison when one QRT isn’t fully done yet

Month on Month, Quarter on Quarter or Year on Year comparisons are an absolute standard in many analysis and/or dashboards.

Recently the question came up how to do a quarter on quarter analysis on a yearly basis (that is, compare Q1 to Q1) when the current quarter is not yet finished. Understandably, the user wanted to do full quarter on quarter comparisons when the quarters were done but do a fair comparison when the current quarter wasn’t yet finished. I.e. for example compare values by 27 of February with the same of the previous year

The data

For this example I am sing just a sample of random numbers along dates created in Excel.

The data ends on 19 February 2021 and starts on 1 January 2019. So we can fair comparisons for all quarters in 2020 and the 2021 Q1 is our concern.

Here is what we do

First, we need to define which date is the last available date in our data source. We do this using an LOD. Notice that when leaving out the “FIXED” we basically have this evaluate every single row in the data source

//Max Date
{ MAX( [Date] ) }

With this done, we can now evaluate if a quarter is done or still ongoing:

//TF is full quarter
[Max Date] >= DATETRUNC('quarter',DATEADD('quarter',1,[Date]))

So we check if the maximum date in the data is bigger or equal to the (row’s) date which we first increase by one quarter and then truncate it back to a quarter.

By truncating, we put the date back to the first day of the quarter. So, sticking with the above dates a date of say 14 March 2019 will be first increased to 14 May 2019 and then truncated to the quarter which yields 1 April 2019 (i.e. the first date of the 2nd quarter).

So all dates as of 1 January 2021 until 19 Feburuary 2021 (the last date in the data and thus the max date we calculatd) will yield 1 April 2021 and therefore, the check will be false.

For all other dates, the check will be true.

Now we can do the calculation that will return the fair comparison values

//Time adjusted Sales | AGG
SUM(
        IF [Date]>= DATEADD('year', -1,  DATETRUNC('quarter', [Max Date])) 
        AND 
        [Date]<= DATEADD('year', -1,  [Max Date]) THEN [Sales] END
    )

This will give us the sum of sales when the date of the previous year is equal to or bigger than the quarter of the most recent date put into last year (so for 19 February 2021 this part yields 1 January 2020 as it is the truncated quarter but back one year) and smaller than the the max date’s value less one year.

With that done we can conclude the final calculation:

//QRT on QRT Deviation Sales | TC
IF MAX([TF is full quarter]) THEN
    SUM([Sales]) - LOOKUP(SUM([Sales]),-4)
ELSE
    SUM([Sales]) - LOOKUP([Time adjusted sales | AGG],-4)
END

The MAX of a True/False is equal to “TRUE” so the first part is just a bit shorter for checking if the TF condition is true. If yes, i.e. it is a full quarter, we just do a table calc and calculate the difference between the quarters year-on-year.

If not, then instead of taking the sales we take the time adjusted sales to keep the comparison fair.

Find the tableau workbook here

And that is it for today. Nothing spectacular but maybe useful to one or the other.

As always appreciate your feedback.

Until next time.

Steffen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s