topic Re: Rolling 6 Month Sum in Scripting in QlikView

Rolling 6 Month Sum in Scripting

Mon, 21 Jul 2014 02:29:08 GMT

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.

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

Re: Rolling 6 Month Sum in Scripting

jagan — Mon, 21 Jul 2014 02:38:40 GMT

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.

Re: Rolling 6 Month Sum in Scripting

Mon, 21 Jul 2014 03:36:03 GMT

You can consider the 'AsOf' tables approach.

Have a look at this.

Calculating rolling n-period totals, averages or other aggregations

Thanks,

Ram

Re: Rolling 6 Month Sum in Scripting

Mon, 21 Jul 2014 04:56:50 GMT

Hi Jagan:

Can you give me an example of how to use Peek() with its parameters?

Thank you.

Re: Rolling 6 Month Sum in Scripting

jagan — Mon, 21 Jul 2014 05:23:57 GMT

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.

Re: Rolling 6 Month Sum in Scripting

Tue, 22 Jul 2014 06:22:31 GMT

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

Re: Rolling 6 Month Sum in Scripting

jagan — Tue, 22 Jul 2014 06:28:54 GMT

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.