ROlling 12 Months

[koushikvutha]

hi

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

[Gysbert_Wassenaar]

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

[Kushal_Chawda · Accepted solution]

see this

Rolling N months

[petter]

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;

[koushikvutha]

Thanks Gysbert. This was really helpful.

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

[koushikvutha]

Perfect. Sorted.

Cheers.