topic Re: Rolling Monthly Average - In Script in QlikView

Rolling Monthly Average - In Script

lorenwood — Mon, 28 Jan 2013 22:26:40 GMT

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

Oleg_Troyansky — Mon, 28 Jan 2013 23:28:49 GMT

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

lorenwood — Tue, 29 Jan 2013 15:20:02 GMT

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

nagaiank — Tue, 29 Jan 2013 15:35:39 GMT

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);

Re: Rolling Monthly Average - In Script

lorenwood — Tue, 29 Jan 2013 16:10:13 GMT

Thanks Kris, this works and helps me a lot