Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 7 days, etc)

Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you want on top of these fields, like Year Over Year, % Differences, etc. Aaaand one of the best parts is everything will update dynamically so you never need to touch them again!

Desired output:

Below you will find a list of common date comparison calculations. All references to the field ‘sales’ can be replaced with the metric you wish to display. You can replace all references to the parameter ‘${AsOfDate}’ with the function ‘now()’ if you want it to be based on today’s date. Or, if you want to base it of the latest date in your data set, then first create a calculated field like this and reference that date field as your end date instead:

Max Date:
maxOver({your date field}, [ ], PRE_AGG)

Between dateDiff(), addDateTime(), truncDate(), and extract() you can define whatever periods you want (and there are often multiple ways to do each one, these are just some proven examples).

Today:

ifelse(datediff({order_date},${AsOfDate}) = 0,sales,0)

Yesterday:

ifelse(datediff({order_date},${AsOfDate}) = 1,sales,0)

Last 90 Days:

ifelse(datediff({order_date},${AsOfDate}) <=90, sales,0)

Last 2 Weeks:

ifelse(datediff({order_date},${AsOfDate},"WK") <=2, sales,0)

This year (aka Year to Date, YTD):

ifelse(datediff({order_date},${AsOfDate},"YYYY") = 0, sales, 0)

Last year:

ifelse(datediff({order_date},${AsOfDate},"YYYY") = 1, sales, 0)

Last year to date (aka LYTD):

ifelse({order_date} >= adddatetime(-1,"YYYY",truncdate("YYYY",${AsOfDate})) AND {order_date} <= adddatetime(-1,"YYYY",${AsOfDate}),sales,0)

This month (aka Month to Date, MTD):

ifelse(datediff({order_date},${AsOfDate},"MM") = 0, sales, 0)

Last month:

ifelse(datediff({order_date},${AsOfDate},"MM") = 1, sales, 0)

Same month last year:

ifelse(datediff({order_date},${AsOfDate},"MM") = 12, sales, 0)

This Quarter:

ifelse(datediff({order_date},${AsOfDate},"Q") = 0, sales, 0)

Last Quarter:

ifelse(datediff({order_date},${AsOfDate},"Q") = 1, sales, 0)

Same Quarter Last Year:

ifelse(datediff({order_date},${AsOfDate},"Q") = 4, sales, 0)

MoM % Diff:

(sum({This Month}) - sum({Last Month})) / sum({Last Month})

YoY % Diff:

(sum({This Year}) - sum({Last Year})) / sum({Last Year})

Monthly YoY % Diff:

(sum({This Month}) - sum({This Month Last Year})) / sum({This Month Last Year})

QoQ% Diff:

(sum({ This Quarter}) - sum({Last Quarter}))/sum({Last Quarter}))

11 Likes

One of the best summaries I’ve seen for this. THANK YOU!

1 Like

Thank you for sharing this solution.

You having “Rolling 7 Days” in your title, but provided no examples. I am currently trying to figure out how to write a rolling seven day distinct count calculation, but finding nothing. Any ideas?

Josh

Hi Joshua - it would be similar to the last 90 days calc, but use 7 as the number and return the field you want to do a distinct count of instead of Sales, and return NULL instead of 0. Then when you drag and drop it into the visual choose a Count Distinct, or in your calculation wrap the whole thing with distinctCount().

What if my table has date as a column? something like this
image
wouldn’t it just show those values as zero rather than actually filtering the unwanted rows out?

Hi rbrady - apologies not sure I understand. With these calculations we are trying to avoid filtering out rows because each column in the final table needs to show different periods (and hence different rows). So you are right that any rows outside of the range we define using the dateDiffs will have a value of 0, but thats what we want (becuase we might needs those rows for a different column/calculation and cant filter them out). If you actually want to filter the rows out then I would recommend using a Relative Date Filter.

Sorry for my delayed response. My goal is to have a table that shows only rows which have dates within a specific range specified by controls. However the rows that are shown are not the only ones being used for calculations. For example lets say I set my controls to only the month of May. So my table is only showing rows for 5/1/2022 - 5/31/2022. But in my table I have a column “sum of previous month” which shows me what my sales were on each day of the previous month. So even though I only want my user viewing rows with dates 5/1/2022 - 5/31/2022, I don’t necessarily want to filter out records with dates 4/1/2022 - 4/30/2022, because then my “sum of previous month” column for May won’t be able to access the values from April.

Hi Jesse, How do we find out the periodic differences when the field is a calculated field and is in percentage terms?

@Tanisha_Shetty you would need to build that ifelse logic into the numerator and denominator for the calculations that compute your percentage, then do the period over period comparison calc using those. Something like:

  • Period 1 percentage: sum(ifelse(whatever logic for period 1, numerator, 0)) / sum(ifelse(whatever logic for period 1, denominator, 0))
  • Period 2 percentage: sum(ifelse(whatever logic for period 2, numerator, 0)) / sum(ifelse(whatever logic for period 2, denominator, 0))
  • Period over period: (Period 2 / Period 1) - 1

@rbrady - I see, this is a little different because you actually want to show multiple date values in the table, along with the period over period comparisons, so your date field will wind up being part of your GROUP BY and the technique describe in this post wont work. You can acheive what you want by using Table Calculation functions to do your period over period calcs (not like the technique in this post, but rather using functions like periodOverPeriodDifference and periodOverPeriodPercentDifference, and then change your filter into a ‘hider’ by filtering on a Table Calculation version of your date field, which will cause that filter to happen later in the query pipeline and will result is keeping all your data in the query and just ‘hiding’ the data points you don’t want at the end. That last step is covered in this post:

1 Like

I see, thank you for the direction!

Thanks Jesse, I will try to implement this. :slight_smile: