Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Aggregating/grouping by four rolling weeks

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!

10 Replies
Not applicable

Re: Aggregating/grouping by four rolling weeks

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

Not applicable

Re: Aggregating/grouping by four rolling weeks

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

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!

jonbrough
Valued Contributor

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:

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

Jonathan

Not applicable

Re: Aggregating/grouping by four rolling weeks

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

Not applicable

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!

Not applicable

Re: Aggregating/grouping by four rolling weeks

try this:

LOAD ICSKU,

     qty,

     date,

     WEEK,

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

FROM

....

Not applicable

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.

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

Re: Aggregating/grouping by four rolling weeks

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.

Community Browser