Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need help with a week filter that I have in my application.
The week filter consists of calendar weeks going two years back in the past. My users want the filter to be dynamic and always have the most recent 13 weeks preselected.
I don't want to have set analysis in my calculations, it needs to be done only with the filter.
Any ideas are going to be appreciated!
Best Regards,
Shelly
Depending on which week numbering system you use and what your definition of "Most recent 13 weeks" is, this could be simple or tricky, but the general approach would be the same - you'd need to calculate whether or not a week is in the most recent 13 using some combination of formulas. Assuming your weeks are actually date duals (created using WeekName() or otherwise), an example might be:
=Week>=Max({1}Week)-91 // 91 days are 13 weeks
Even if you need a more complex statement for evaluating the last 13 weeks, you should be able to get it done using a combination of if() statements, but this can get somewhat tricky depending on the specifics as weeks can be partial, years can have a different number of weeks (52, 53, or 54), etc.
You could also consider a relatively simple script-side solution flagging the last 13 weeks by selecting distinct weeks, sorted descending, and only keeping the top 13 rows.