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!
what is the range of the data? only year 2012 ?
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;
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!
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
post small sample of orders table (f.ex. in qvd or xls)
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!
try this:
LOAD ICSKU,
qty,
date,
WEEK,
floor((Week(Date#(date,'YYYYMMDD'))-week(today())-4)/4)+1 as BUCKET
FROM
....
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!
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.