Knowledge Drop

How do I use custom filters to get a YoY of the last X complete days within the same month?

  • 5 April 2021
  • 0 replies
  • 30 views

Last tested: Apr 23, 2019

 

Use this awesome filter:

(extract_months(${date}) = extract_months(now())) AND extract_days((${date})) <= extract_days(now())-1

AND

extract_days((${date})) >= extract_days(now())-x

AND

(extract_years(${sources.date_date}) = (extract_years(now()))

OR extract_years(${sources.date_date}) = (extract_years(now())-1))

We'd want to replace the "x" at the end of extract_days((${date})) >= extract_days(now())-x with the number of days you want. For example, if we want a YoY for the last 5 complete days, we'd replace the x with 5.

 

Note: This will only work if we're trying to select days from the same month. This calc relies on the extract_days function and subtraction. So for example if it's May 2nd and we want the last 30 days, the filter won't work.

This is because `extract_days((${date})) >= extract_days(now())-30` would yield:
-28, -27, -26, ..., 0, 1 and 5/-28 isn't a date.

 

This content is subject to limited support.                

 


0 replies

Be the first to reply!

Reply