Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to turn the following transaction table...
Article | Date | Transaction | Value |
---|---|---|---|
A | 3-1-2011 | 1 | 10 |
A | 17-1-2011 | 2 | 20 |
A | 14-2-2011 | 3 | 10 |
A | 13-3-2011 | 4 | 60 |
A | 18-4-2011 | 5 | 70 |
A | 19-4-2011 | 6 | 30 |
A | 22-6-2011 | 7 | 50 |
A | 18-8-2011 | 8 | 20 |
A | 31-8-2011 | 9 | 10 |
A | 22-9-2011 | 10 | 60 |
A | 1-11-2011 | 11 | 90 |
B | 14-2-2011 | 12 | 11 |
B | 17-2-2011 | 13 | 22 |
B | 15-4-2011 | 14 | 44 |
B | 17-5-2011 | 15 | 33 |
B | 31-5-2011 | 16 | 88 |
B | 29-6-2011 | 17 | 55 |
B | 31-7-2011 | 18 | 66 |
B | 18-8-2011 | 19 | 99 |
B | 11-10-2011 | 20 | 23 |
...into YTD accumulation of 'Value' over 'Article' and 'Month'. Something like this...
Article | Month | YTD | Month end |
---|---|---|---|
441 | 30-11-2011 | ||
A | Jan | 30 | 31-1-2011 |
A | Feb | 40 | 28-2-2011 |
A | March | 100 | 31-3-2011 |
A | Apr | 200 | 30-4-2011 |
A | Juni | 250 | 30-6-2011 |
A | Aug | 280 | 31-8-2011 |
A | Sep | 340 | 30-9-2011 |
A | Nov | 430 | 30-11-2011 |
B | Feb | 33 | 28-2-2011 |
B | Apr | 77 | 30-4-2011 |
B | May | 198 | 31-5-2011 |
B | Juni | 253 | 30-6-2011 |
B | Juli | 319 | 31-7-2011 |
B | Aug | 418 | 31-8-2011 |
B | Oct | 441 | 31-10-2011 |
Can this be accomplished by Set Analysis?
the following syntax does not work:
Sum({<Date = {'>=$(=YearStart(aggr(NODISTINCT Max({1} Date),Article,Month)))} <= $(=aggr(NODISTINCT Max({1} Date),Article,Month))'}>}Value)
- I know it's also possible to achieve the same goal using YTD flags in the script but in this case, i rather not.
- attached .qvw as example
appreciate your help!
Best regards,
Dror
-
Hi Dror,
Check the attached application. I'm using RangeSum() function and Above() in a new straight table.
RangeSum(Above(Sum(Value), 0, NoOfRows()))
Hope that helps.
Miguel
Miquel,
Thanks! (again).
This, definitly answer my problem.
However, i'm curius wether it's possible to use Set Analysis to aggregate value beyond the dimmension selection (month).
Dror
Hi Dror,
Not with your model. This is a very recurring question in the QlikCommunity, you can find it also named rolling months. There are several solutions, the most accepted purportedly is to create a table in the script AsOf for each possible date you have in your model. Anyway, using this method is usually cleaner and don't require to create that table in the script, and this is one of the main uses of RangeSum() or range functions.
Hope that makes sense.
Miguel
EDIT: It's has been discussed thoroughly in the QlikCommunity as well, and might worth noting here that set analysis is not "good" for this kind of loops, since the match needs to be done in a row per row basis (or dimension value by dimension value basis), while set analysis is evaluated once and for the whole chart. Aggr() function doesn't work within a set analysis, and Max(Date) within set analysis will return always the maximum date for the whole chart, regardless the Article or any other dimensions.
Miguel,
To be honest, I've posted the YTD as example for a bigger problem in my production environment.
I was asked to produce the following overview in a pivot chart:
Store | Month | Qty sold | Inventory (end of period) |
---|---|---|---|
Paris | Jan | 20 | 450 |
Paris | Feb | 15 | 445 |
... | ... | ... | ... |
- Qty sale and inventory stand are located on the same Transaction table
- Transaction table contains 6.5 million records
- Transaction table is binded to MasterCalendar by 'Date' field
- Qty sold expression is quite straightforward: Sum(QtySold)
- Inventory calculation is quite more difficult:
I''ve tried anything from IF statement, Set Analysis, Date island, crazy intervalMatch (25M records...) etc...It's either don't work or too much resources intensive.
Any idea how i can solve this?
Best regards,
Dror