Rolling Monthly Average - In Script

[lorenwood]

Hello everyone, I am trying to calculate the rolling average for the month (See attached file) (US Dollars to Euro)

so if day 1 the rate is 1.4000 and day 2 the rate is 1.4500 day 2 avg rate will be 1.4250.  for Day 3 and the rate is 1.4600 and rolling average will be 1.4366 etc.

This will need to change starting with first day of the month, so on Jan 1, the average starts new.

Not sure how to get this going in the script so any help provided would be great

Thanks

[Oleg_Troyansky]

I'd recommend using Peek() and Previous() to accumulate rolling total and rolling count within each month (the average = rolling total/rolling count), and restart the total and the count every time the new month starts.

[lorenwood]

Oleg thanks for responding, just as an example, the peek () function will help with the actual count/total for that day and the previous () would be to used to accumulate the count/total. Do you think you could provide some sample code?

Thanks Again
Loren

[nagaiank · Accepted solution]

Attached is a sample application using Oleg's suggestion.

The script used is:

Data:

LOAD *, CumDailyRate/CumDayCount as CalcAvg;

LOAD *, if(CurrMonthKey = Previous(CurrMonthKey), RangeSum(DailyRate, Peek('CumDailyRate')), DailyRate) as CumDailyRate

      , if(CurrMonthKey = Previous(CurrMonthKey), RangeSum(1, Peek('CumDayCount')), 1) as CumDayCount;

LOAD CurrMonthKey,

     DailyRate,

     AvgRollingMonthRate,

     Date

FROM RollingAvgExample.xlsx

(ooxml, embedded labels, table is Sheet1);

[lorenwood]

Thanks Kris, this works and helps me a lot