# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor II

## 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)
Tags (1)
11 Replies
Contributor II

## Re: Calculate Rolling Average for 2 years data

I want this in Script, not in Edit expression

Contributor

## Re: Calculate Rolling Average for 2 years data

Hi Ram,

Try  something like this.

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

Contributor

## Re: Calculate Rolling Average for 2 years data

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

Regards,

Contributor II

## Re: Calculate Rolling Average for 2 years data

Need to add in script, not in edit expression

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

Contributor

## Re: Calculate Rolling Average for 2 years data

Do the same in back end....

Contributor III

## Re: Calculate Rolling Average for 2 years data

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

Ram

Try it Peek function

Contributor III

## Re: Calculate Rolling Average for 2 years data

Here you go :

Let vNbMonthsToLetAsItIs=6;

let vNbOfMonthsToTakeInAvg=6;

Temp_Data1:

"Month" as Period,

Cost

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

(ooxml, embedded labels, table is Feuil1);

// Rest of script

Temp_Data2:

recno() as PeriodID,

Period,

Cost

resident Temp_Data1

order by Period;

join(Temp_Data2)

recno() as PeriodID_2,

Cost as Cost_2

resident Temp_Data1

order by Period;

Drop table Temp_Data1;

NoConcatenate

Temp_Data3:

*

resident Temp_Data2

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

Drop table Temp_Data2;

NoConcatenate

Final:

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;

Contributor II

## Re: Calculate Rolling Average for 2 years data

Hi Thomas,

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

Contributor III

## Re: Calculate Rolling Average for 2 years data

Could you post your code ?

And a screenshot of the error ?