Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi:
Can anyone teach me how to do rolling sum in scripting level?
I understand how to use RangeSum and Above functions in the expression but Above is not available in the scripting.
Thank you very much in advance.
PC
PS: Here are two examples.
It would be great if I can do something like this:
Date | Head Count | Rolling 6 Month Head Count |
---|---|---|
01-01-2013 | 1 | 1 |
01-02-2013 | 1 | 2 |
01-03-2013 | 1 | 3 |
01-04-2013 | 1 | 4 |
01-05-2013 | 1 | 5 |
01-06-2013 | 1 | 6 |
01-07-2013 | 1 | 6 |
01-08-2013 | 1 | 6 |
01-09-2013 | 1 | 6 |
01-10-2013 | 1 | 6 |
01-11-2013 | 1 | 6 |
01-12-2013 | 1 | 6 |
01-01-2014 | 0 | 5 |
01-02-2014 | 0 | 4 |
01-03-2014 | 0 | 3 |
01-04-2014 | 0 | 2 |
01-05-2014 | 0 | 1 |
01-06-2014 | 0 | 0 |
Another example would be:
Date | Head Count | Rolling 6 Month Head Count |
---|---|---|
01-01-2013 | 1 | 1 |
01-02-2013 | 1 | 2 |
01-03-2013 | 1 | 3 |
01-04-2013 | 1 | 4 |
01-05-2013 | 0 | 4 |
01-06-2013 | 0 | 4 |
01-06-2013 | 0 | 3 |
01-07-2013 | 0 | 2 |
01-08-2013 | 0 | 1 |
01-09-2013 | 0 | 0 |
Hi,
In script you can use the Peek() or Previous() to get the previous row values. I think Peek() would be the best pick, because you can give the offset.
Regards,
Jagan.
You can consider the 'AsOf' tables approach.
Have a look at this.
Calculating rolling n-period totals, averages or other aggregations
Thanks,
Ram
Hi Jagan:
Can you give me an example of how to use Peek() with its parameters?
Thank you.
PC
Hi,
Try this script
Test:
LOAD
*,
Numsum([Head Count], Previous([Head Count]), Previous(Previous([Head Count])), Previous(Previous(Previous([Head Count]))), Previous(Previous(Previous(Previous([Head Count])))), Previous(Previous(Previous(Previous(Previous([Head Count])))))) AS [Rolling 6 Month Head Count];
LOAD
Date(Date#(Date, 'DD-MM-YYYY')) AS Date,
[Head Count]
FROM
[http://community.qlik.com/thread/126379]
(html, codepage is 1252, embedded labels, table is @1);
Regards,
jagan.
Hi Jagan:
I thought you were going to use PEEK() as an example.
But this PREVIOUS() works fine as well. And it is simply. Just need to repeat the function several times.
For anyone want something more sophisticated, maybe this is not what you are looking for.
Thank you
Paco
Hi,
Tried using Peek(), but getting some issues, that's why I did this using Previous(), anyway we are using only 6 rolling months so it is not a problem.
Regards,
Jagan.