topic Re: ROlling 12 Months in QlikView

ROlling 12 Months

koushikvutha — Fri, 03 Jun 2016 15:03:06 GMT

Please see attached data. Any method to achieve this in edit script?

Re: ROlling 12 Months

Gysbert_Wassenaar — Fri, 03 Jun 2016 15:41:41 GMT

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

Re: ROlling 12 Months

Kushal_Chawda — Fri, 03 Jun 2016 15:43:27 GMT

see this

Rolling N months

Re: ROlling 12 Months

petter — Fri, 03 Jun 2016 15:50:14 GMT

It can be achieved by using Peek() and three temporary Fields:

LOAD BusinessUnit,
MonthYear,
Value,
Alt( Peek('Value',-11), Null()) AS FirstOne,
Alt( Peek('RunningTotal') , 0) + Value AS RunningTotal,
Alt( Peek('FirstOne') , 0) + Alt(Peek('RunningFirstOne'),0) AS RunningFirstOne,
If( RecNo() > 11 ,Peek('RunningTotal') + Value - Peek('FirstOne') - Peek('RunningFirstOne')) AS Rolling12Months,
F4,
[Expectation - Rolling 12 months],
F,
G,
H,
I
FROM
[12MonthsTest.xlsx]
(ooxml, embedded labels, table is Sheet1);

DROP FIELDS FirstOne, RunningTotal, RunningFirstOne;

Re: ROlling 12 Months

koushikvutha — Fri, 03 Jun 2016 15:57:03 GMT

Thanks Gysbert. This was really helpful.

I could achieve it using accumulate but wanted a clever workaround in script.

Re: ROlling 12 Months

koushikvutha — Fri, 03 Jun 2016 15:57:34 GMT

Perfect. Sorted.

Cheers.