Aggregating/grouping by four rolling weeks

[#1]

Dear all,

I have a script which is grouping, by yearly week, sales per product.

LOAD ONLY(CalendarWeekOfYear) as WeekDemand,
ICSKU,
Sum(LQORD) as WeekQtyOrd
RESIDENT Orders
GROUP BY CalendarWeekOfYear, ICSKU;

I would like to obtain also, a sum of the 4 weeks before, and the 4 before and so on, in other words:

Today, week0.

Groups:

week -1 > week -4

week -5 > week -8

week -9 > week -12

etc.etc.

Is there a simple way to achieve this in the script?

Maybe with rangesum and before?

I have been browsing a bit about aggregated sums, but I couldn't find any hint about grouping a rolling bucket of n weeks.

Thanks!

[#2 · Accepted solution]

I manged with the following script

LOAD ICSKU, 
     Qty as BucketQty, 
     DateInterpret as BucketDate, 
     floor((((DateInterpret)-WeekStart(today(), -1))-7)/28) as BUCKET
RESIDENT

This way, a bucket of 4 weeks counting back from the last Sunday(included) is created and it works fine over the transition to the new year!