7 Replies Latest reply: Sep 30, 2016 3:49 AM by Ganesh Bagavathi

# Rolling Sum/Rolling Average at Script

Hello,

I am looking for the functionality to introduce a column for Rolling Sum or Rolling Average in the attached Excel. It is day wise data and the rolling sum has to be reset at the end of every month.

Thanks,

• ###### Re: Rolling Sum/Rolling Average at Script

Something like this

RollingMonths:

load Period, Period as RollingPeriod, year(Period) as RollingYear Resident Data1;

• ###### Re: Rolling Sum/Rolling Average at Script

May be this?

Table:

MonthName(CCY_EXCH_VALID_DT) as MonthYear,

FROM_CCY_CD,

TO_CCY_CD,

RATE_AMT

FROM

[Rolling Sum.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

If(FROM_CCY_CD = Previous(FROM_CCY_CD) and MonthYear = Previous(MonthYear), RangeAvg(Peek('RangeAvg'), RATE_AMT), RATE_AMT) as RangeAvg,

If(FROM_CCY_CD = Previous(FROM_CCY_CD) and MonthYear = Previous(MonthYear), RangeSum(Peek('RangeSum'), RATE_AMT), RATE_AMT) as RangeSum

Resident Table

Order By FROM_CCY_CD, CCY_EXCH_VALID_DT;

DROP Table Table;

• ###### Re: Rolling Sum/Rolling Average at Script

Hello Sunny,

Thanks for your quick response as usual

The RangeSum seems to be working perfectly, however the RangeAvg is not what i expected.

I was expecting something like below,

Date - Range Avg

1/1/2015 - 0.8173

1/2/2015 - (0.8173+0.8141)/2=0.8157

1/3/2015-2.4370/3=0.8123

Guess, RangeAvg function works in a different way.

• ###### Re: Rolling Sum/Rolling Average at Script

Try this out

Table:

MonthName(CCY_EXCH_VALID_DT) as MonthYear,

FROM_CCY_CD,

TO_CCY_CD,

RATE_AMT

FROM

[Rolling Sum.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

RangeSum/RangeSumDeno as RangeAvg;

// If(FROM_CCY_CD = Previous(FROM_CCY_CD) and MonthYear = Previous(MonthYear), RangeAvg(Peek('RangeAvg'), RATE_AMT), RATE_AMT) as RangeAvg,

If(FROM_CCY_CD = Previous(FROM_CCY_CD) and MonthYear = Previous(MonthYear), RangeSum(Peek('RangeSum'), RATE_AMT), RATE_AMT) as RangeSum,

If(FROM_CCY_CD = Previous(FROM_CCY_CD) and MonthYear = Previous(MonthYear), RangeSum(Peek('RangeSumDeno'), 1), 1) as RangeSumDeno

Resident Table

Order By FROM_CCY_CD, CCY_EXCH_VALID_DT;

DROP Table Table;

• ###### Re: Rolling Sum/Rolling Average at Script

Dates are repeated with this solution.

I would achieve the Average by dividing the RangeSum column with the Day part of date then.

• ###### Re: Rolling Sum/Rolling Average at Script

Sorry, just noticed that the data in the excel is repeated. I have completed by dividing the RangeSum by Day of date column.

Thanks a lot.