topic Re: Aggregating/grouping by four rolling weeks in QlikView

Aggregating/grouping by four rolling weeks

Tue, 04 Dec 2012 15:05:20 GMT

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!

Re: Aggregating/grouping by four rolling weeks

Tue, 04 Dec 2012 19:00:17 GMT

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

Re: Aggregating/grouping by four rolling weeks

Tue, 04 Dec 2012 19:08:08 GMT

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;

Re: Aggregating/grouping by four rolling weeks

Wed, 05 Dec 2012 12:45:38 GMT

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

Anonymous — Wed, 05 Dec 2012 13:00:50 GMT

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

Re: Aggregating/grouping by four rolling weeks

Wed, 05 Dec 2012 13:31:47 GMT

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

Re: Aggregating/grouping by four rolling weeks

Wed, 05 Dec 2012 16:02:26 GMT

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

Wed, 05 Dec 2012 16:57:09 GMT

try this:

LOAD ICSKU,

qty,

date,

WEEK,

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

FROM

....

Re: Aggregating/grouping by four rolling weeks

Wed, 05 Dec 2012 18:43:01 GMT

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!

Re: Aggregating/grouping by four rolling weeks

Wed, 05 Dec 2012 19:13:13 GMT

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.

Re: Aggregating/grouping by four rolling weeks

Wed, 05 Dec 2012 21:16:29 GMT

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!