Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rolling 6 Month Sum in Scripting

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:

DateHead CountRolling 6 Month Head Count
01-01-201311
01-02-201312

01-03-2013

13
01-04-201314
01-05-201315
01-06-201316
01-07-201316
01-08-201316
01-09-201316
01-10-201316
01-11-201316
01-12-201316
01-01-201405
01-02-201404
01-03-201403
01-04-201402
01-05-201401
01-06-201400

Another example would be:

DateHead CountRolling 6 Month Head Count
01-01-201311
01-02-201312

01-03-2013

13
01-04-201314
01-05-201304
01-06-201304
01-06-201303
01-07-201302
01-08-201301
01-09-201300
6 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

You can consider the 'AsOf' tables approach.

Have a look at this.

Calculating rolling n-period totals, averages or other aggregations

Thanks,

Ram

Not applicable
Author

Hi Jagan:

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

Thank you.

PC

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.