Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!