10 Replies Latest reply: Dec 5, 2012 4:16 PM by Erik Furlanis

# Aggregating/grouping by four rolling weeks

Dear all,

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

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!

• ###### Re: Aggregating/grouping by four rolling weeks

what is the range of the data? only year  2012 ?

• ###### Re: Aggregating/grouping by four rolling weeks

floor((week(today())-CalendarWeekOfYear)/4) as group,

Sum(LQORD) as WeekQtyOrd

RESIDENT Orders

GROUP BY floor((week(today())-CalendarWeekOfYear)/4, ICSKU;

• ###### Re: Aggregating/grouping by four rolling weeks

thank you for your input, but is not working as expected.

I am returned just one value, and I can't understand to which range of weeks this single value is referrng to.

Moreover (but that happened many other times in my short QV scripting experience!) I can't figure out how your script should yield my requirement...

Big thanks!

• ###### Re: Aggregating/grouping by four rolling weeks

post small sample of orders table (f.ex. in qvd or xls)

• ###### Re: Aggregating/grouping by four rolling weeks

Thank you again for your help.

I attached a sample made in Excel.

I'm trying to obtain what you can see in the last column, highlighted.

Of course, for each date there are hundreds of line in my Orders table (in the sample just one per day), and I need to keep the ICSKU details.

Every new week it will need to change the reference interval, as the weeks are passying by.

For the moment being, 6 buckets of 28 days will do.

I was thinking of someting in this direction:

WeekEnd(DateInterpret, -1) - WeekStart(DateInterpret, -4)

But got kinda stuck.

thank you again!

• ###### Re: Aggregating/grouping by four rolling weeks

try this:

qty,

date,

WEEK,

floor((Week(Date#(date,'YYYYMMDD'))-week(today())-4)/4)+1 as BUCKET

FROM

....

• ###### Re: Aggregating/grouping by four rolling weeks

thank you for your try, but I must admit it is not working.

And I am not able to even tell what is wrong, the buckets are just so randomly assigned to the weeks.

I did a bit of research, and it is about rounding down basically. It is not clear to me the purpose, as when you are subtracting two dates, you always have an integer anyway...

===

EDIT: I think I'm grasping the purpose of the floor function, it is to attribute an integer to the BUCKET number!

• ###### Re: Aggregating/grouping by four rolling weeks

actually, this simple script is working for me

LQORD as BucketQty,
DateInterpret as BucketDate,
floor((Week(DateInterpret)-week(Today()))/4) as BUCKET

Easy peasy!

Now the next big challenge is how to distinguis the weeks of different year, as, for example, in Bucket -1, besides the correct dates (20121105 - 20121202), it is including 20131113, so that would be the -1 bucket for the same week# of next year. This seems more difficult to tackle, as qlikview can't handle a

Year(Date)&Week(Date)

...I think...

and anyway is going to become tricky also with the beginning of the new year, when bucket -1 will be, e.g., equal to weeks 2,1,52,51.

• ###### Re: Aggregating/grouping by four rolling weeks

I manged with the following script

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!

• ###### Re: Aggregating/grouping by four rolling weeks

Not sure how to do in the script, but this post was helpful in allowing me tpo do within a chart: