Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

Please give me your suggestions.

Thanks,

6 Replies
rupamjyotidas
Specialist
Specialist

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;


Capture.PNG

Not applicable
Author

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;

Not applicable
Author

Dates are repeated with this solution.

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

Not applicable
Author

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.