topic Re: Rolling Sum/Rolling Average at Script in QlikView

Rolling Sum/Rolling Average at Script

Thu, 29 Sep 2016 13:44:35 GMT

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,

Re: Rolling Sum/Rolling Average at Script

rupamjyotidas — Thu, 29 Sep 2016 16:29:30 GMT

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;

Re: Rolling Sum/Rolling Average at Script

sunny_talwar — Thu, 29 Sep 2016 17:05:14 GMT

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;

Re: Rolling Sum/Rolling Average at Script

Thu, 29 Sep 2016 17:22:13 GMT

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

sunny_talwar — Thu, 29 Sep 2016 17:37:25 GMT

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;

Re: Rolling Sum/Rolling Average at Script

Fri, 30 Sep 2016 07:49:42 GMT

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

Fri, 30 Sep 2016 08:00:05 GMT

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.