Calculate Rolling Average for 2 years data

[rammuthiah]

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)

[rammuthiah]

I want this in Script, not in Edit expression

[jegadeesan]

Hi Ram,

Try  something like this.

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

[jegadeesan]

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

Regards,

Jegadeesan G

[rammuthiah]

Need to add in script, not in edit expression

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

[jegadeesan]

Do the same in back end....

[muniyandi]

Hi Jegadeesan,

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

Ram

Try it Peek function

[thomaslg_wq]

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]

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]

Could you post your code ?

And a screenshot of the error ?