Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Tags (1)
6 Replies
MVP & Luminary
MVP & Luminary

Re: Rolling 6 Month Sum in Scripting

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

Re: Rolling 6 Month Sum in Scripting

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

Re: Rolling 6 Month Sum in Scripting

Hi Jagan:

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

Thank you.

PC

MVP & Luminary
MVP & Luminary

Re: Rolling 6 Month Sum in Scripting

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

Re: Rolling 6 Month Sum in Scripting

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

MVP & Luminary
MVP & Luminary

Re: Rolling 6 Month Sum in Scripting

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.