Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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;
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.
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;
Dates are repeated with this solution.
I would achieve the Average by dividing the RangeSum column with the Day part of date then.
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.