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

[david_chiaramon]

Knowledge Drop

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.