Knowledge Drop
Last tested: Mar 17, 2020
Like many people out there, you like to automate processes and cringe at the idea that is "manual." Contrary to popular belief, also like many people out there you like to create weekly rollup ranges in you Explore. Well by golly do I have a solution for you.
The Dirty:
(${week_field} >= add_days(-1 * mod(diff_days(date(2008,01,01), now()) + 1, 7), now()) AND ${week_field} <= add_days(28,now()) AND (extract_years(${week_field}) = extract_years(now())))
OR
(${week_field} <= add_days(-1 * mod(diff_days(date(2008,01,01),now()) + 1, 7), now()) AND ${week_field} >= add_days(-28,now()) AND (extract_years(${week_field}) = extract_years(now())))
To-Note: Order of the two filter conditions doesn't particularly matter here. The only substitutions necessary are swapping out week_field for your week date field.
The first filter condition calculates for 4 weeks from now. Whereas the second calculates for weeks prior to now. Within each calc we are comparing a week field to this fun bit add_days(-1 * mod(diff_days(date(2008,01,01), now()) + 1, 7), now()). Basically what we are doing here is using a calculation to grab the week from a date...why might you ask are we doing this nasty, well that's because we are limited to the functions available to us within the custom filter and need to grab the week manually.
From there we are comparing the week field to 4 weeks from now (in this example). We can just convert 1 week to 7 days and multiply that to get the number of weeks we want prior to or from now. Finally we compare the year from the week field to now to make sure we are only pulling data for the current year.
And that's it friends, plug and play as they say..
LinkedIn
Twitter