Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregating/grouping by four rolling weeks

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!

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

10 Replies
Not applicable
Author

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

Not applicable
Author

i hope this will help You.

LOAD ICSKU,

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

Sum(LQORD) as WeekQtyOrd

RESIDENT Orders

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

Not applicable
Author

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!

Anonymous
Not applicable
Author

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

http://community.qlik.com/thread/38355

Jonathan

Not applicable
Author

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

Not applicable
Author

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!

Not applicable
Author

try this:

LOAD ICSKU,

     qty,

     date,

     WEEK,

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

FROM

....

Not applicable
Author

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.

May I ask you about the floor function you are using?

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!

Not applicable
Author

actually, this simple script is working for me

LOAD ICSKU,

     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.