Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rammuthiah
Creator III
Creator III

Calculate Rolling Average for 2 years data

I am trying to calculate rolling average for 2 years data.

For example :


Month

Cost

Rolling Average

Logic

Apr-16

105

105

First 6 month Cost should be as it is

May-16

110

110

Jun-16

110

110

Jul-16

708

708

Aug-16

709

709

Sep-16

107

107

Oct-16

109

Average(Previous 6 months)

This should be
Average of previous months based on current month

Nov-16

107

Average(Previous 6 months)

Dec-16

506

Average(Previous 6 months)

Jan-17

709

Average(Previous 6 months)

Feb-17

506

Average(Previous 6 months)

Mar-17

200

Average(Previous 6 months)

Apr-17

107

Average(Previous 6 months)

May-17

105

Average(Previous 6 months)

Jun-17

109

Average(Previous 6 months)

Jul-17

909

Average(Previous 6 months)

Aug-17

107

Average(Previous 6 months)

Sep-17

109

Average(Previous 6 months)

Oct-17

106

Average(Previous 6 months)

Nov-17

705

Average(Previous 6 months)

Dec-17

106

Average(Previous 6 months)

Jan-18

112

Average(Previous 6 months)

Feb-18

108

Average(Previous 6 months)

Mar-18

109

Average(Previous 6 months)

Apr-18

112

Average(Previous 6 months)

11 Replies
rammuthiah
Creator III
Creator III
Author

I want this in Script, not in Edit expression

jegadeesan
Creator
Creator

Hi Ram,

Try  something like this.

RangeAvg(Above(sum(Sales),1,24))

jegadeesan
Creator
Creator

Put 6 instead of 24... Is this for last 6 months avg isnt fit....

Regards,

Jegadeesan G

rammuthiah
Creator III
Creator III
Author

Need to add in script, not in edit expression

On 25-Nov-2016 12:58 pm, "Jegadeesan Gopalakrishnan" <

jegadeesan
Creator
Creator

Do the same in back end....

muniyandi
Creator III
Creator III

Hi Jegadeesan,

In Edit script "above" function it won't work.

Ram

Try it Peek function

thomaslg_wq
Creator III
Creator III

Here you go :

// Your parameters

Let vNbMonthsToLetAsItIs=6;

let vNbOfMonthsToTakeInAvg=6;

// Your data load

Temp_Data1:

LOAD

    "Month" as Period,

    Cost

FROM [lib://CommunityTests/RollingAvg.xlsx]

(ooxml, embedded labels, table is Feuil1);

// Rest of script

Temp_Data2:

LOAD

recno() as PeriodID,

Period,

Cost

resident Temp_Data1

order by Period;

join(Temp_Data2)

LOAD

recno() as PeriodID_2,

Cost as Cost_2

resident Temp_Data1

order by Period;

Drop table Temp_Data1;

NoConcatenate

Temp_Data3:

LOAD

*

resident Temp_Data2

where PeriodID>=PeriodID_2-$(vNbOfMonthsToTakeInAvg) and PeriodID<=PeriodID_2;

Drop table Temp_Data2;

NoConcatenate

Final:

Load

avg(Cost) as Cost,

Max(Period) as Period,

PeriodID_2

resident Temp_Data3

where if(PeriodID_2<=$(vNbMonthsToLetAsItIs),PeriodID_2=PeriodID,1=1)

group by PeriodID_2;

Drop table Temp_Data3;

rammuthiah
Creator III
Creator III
Author

Hi Thomas,

Thanks for the reply. While trying the above code, it's getting hanged. Is there any other way to do so?

thomaslg_wq
Creator III
Creator III

Could you post your code ?

And a screenshot of the error ?