Rolling Sum/Rolling Average at Script

[#1]

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.

Please give me your suggestions.

Thanks,

[rupamjyotidas]

Something like this

RollingMonths:

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

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

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

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

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

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

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

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

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

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

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

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

[sunny_talwar]

May be this?

Table:

LOAD CCY_EXCH_VALID_DT,

  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:

LOAD *,

  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;

[#4]

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.

[sunny_talwar]

Try this out

Table:

LOAD CCY_EXCH_VALID_DT,

  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:

LOAD *,

  RangeSum/RangeSumDeno as RangeAvg;

LOAD *,

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

[#6]

Dates are repeated with this solution.

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

[#7]

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.