4 Replies Latest reply: Jan 29, 2013 11:10 AM by Loren Wood

# Rolling Monthly Average - In Script

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

• ###### Re: Rolling Monthly Average - In Script

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.

• ###### Re: Rolling Monthly Average - In Script

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

• ###### Re: Rolling Monthly Average - In Script

Attached is a sample application using Oleg's suggestion.

The script used is:

Data:

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

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

DailyRate,

AvgRollingMonthRate,

Date

FROM RollingAvgExample.xlsx

(ooxml, embedded labels, table is Sheet1);

• ###### Re: Rolling Monthly Average - In Script

Thanks Kris, this works and helps me a lot