Rolling 12 weeks

[M1ndbender]

Hi there,

I am stuck trying to find a way to only return the previous 12 weeks worth of data that will continue to work when they span multiple years. If I was to run it in Jan or Feb I would need it to pull back the 2022 data as well as the rest of the 12 weeks that fall in 2021.

Any help would be appreciated

[BigChris]

Ok, depending on what you need, and your structures etc., you could probably use something along the lines of:

Code Select Expand


[YourDateField] between _add_days(current_date,-(84 + _day_of_week(current_date,1)) and _add_days(current_date,-day_of_week(current_date,1)

Do some testing on the day of week function - I've just written that off the top of my head, so it might not be quite right.

[M1ndbender]

Thank you. I will play around with it. For my own curiosity - what does the 84 do?

[BigChris]

12 * 7 = 84   :D

[M1ndbender]

right   duh  lol

[M1ndbender]

That worked. Thank you very much. I can't seem to find how to close the ticket but this one can be considered closed