11 Replies Latest reply: Nov 29, 2016 3:53 AM by thomas le gall

# 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)
• ###### Re: Calculate Rolling Average for 2 years data

I want this in Script, not in Edit expression

• ###### Re: Calculate Rolling Average for 2 years data

Hi Ram,

Try  something like this.

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

• ###### Re: Calculate Rolling Average for 2 years data

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

Regards,

• ###### 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" <

• ###### Re: Calculate Rolling Average for 2 years data

Do the same in back end....

• ###### Re: Calculate Rolling Average for 2 years data

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

Ram

Try it Peek function

• ###### 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;

• ###### 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?

• ###### Re: Calculate Rolling Average for 2 years data

Could you post your code ?

And a screenshot of the error ?

• ###### Re: Calculate Rolling Average for 2 years data

InvDateMapping:

PNR,

min("InvDt") as MinInvDate

FROM [lib://DB/QVDs/CompleteData.qvd]

(qvd)

Group by PNR;

Excel:

*,

IF(("DFA")<14,'IN-BOUND','OUT-BOUND') as DFAFlag,

IF("ACS"='C','CCS','NACS') as ACSFlag

FROM [lib://DB/QVDs/CompleteData.qvd](qvd)

where ApplyMap('InvDateMapping',PNR)=InvDt

;

 FiscalQuartersMap: MAPPING LOAD * Inline [ MonthNumber, QuarterNumber 1, Q4 2, Q4 3, Q4 4, Q1 5, Q1 6, Q1 7, Q2 8, Q2 9, Q2 10, Q3 11, Q3 12, Q3 ]; Temp: Load min("BOOKING DATE") as minDate, max("BOOKING DATE") as maxDate Resident Excel; Let varMinDate = Num(Peek('minDate', 0, 'Temp')); Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); DROP Table Temp; TempCalendar: LOAD \$(varMinDate) + Iterno()-1 As Num, Date(\$(varMinDate) + IterNo() - 1) as TempDate AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate); MasterCalendar: Load Monthname(TempDate) AS "Month of BOOKING DATE",

//      Year(TempDate) As "Year",

Dual('FY' & Right(YearName(TempDate,0,4),2), YearName(TempDate,0,4)) as "Fiscal Year"

//      ,If(TempDate >= MonthStart(Date(\$(varMaxDate)), -5), monthname(TempDate)) as "Rolling 6-Month Periods",

//         If(TempDate >= MonthStart(Date(\$(varMaxDate)), -11), 'Previous 6 Months', Null())) as "6-Month Periods",

 //      Dual('FY' & Right(YearName(TempDate,0,4),2) & 'Q' & Ceil(Month(QuarterStart(TempDate,-1))/3), QuarterStart(TempDate,-1)/3) as "Fiscal Year Quarter", //      Dual('FY' & Right(YearName(TempDate,0,4),2) & '-' & Month(TempDate), MonthStart(TempDate)) as "Fiscal Year Month"

//      ,ApplyMap('FiscalQuartersMap', month(TempDate), Null()) as "Fiscal Quarter"

 Resident TempCalendar Order By TempDate ASC;

 Drop Table TempCalendar;

FromExcel:

Route

//     &'|'&CurrFY as Segment_CurrFY,

,Avg(Cost) as CurrDACompCostAvg

Resident Excel where DFAFlag='OUT-BOUND'

group by

Route

//     ,    CurrFY

;

rename table Excel to ExcelHisCalc;

Excel:

*,

PNR&'|'&Route&'|'&"InvDt" as UniqueKey

//     ,ApplyMap('FromExcel',Segment_PrevFY,0) as PrevDACompCostAvg

Resident ExcelHisCalc;

Drop Table ExcelHisCalc;

// '14 Days Advanced Purchase' as Policy,

// if(AvgFDA=0,0,(TotalCost)-(AvgFDA)) as OpportunityCost;

PNR&'|'&Route&'|'&"InvDt" as UniqueKey,

PNR ,

//     (PrevDACompCostAvg) as AvgFDA,

sum(Cost) as TotalCost

Resident Excel

where DFAFlag='IN-BOUND'

group by

PNR,

//     PrevDACompCostAvg,

Route,

"InvDt"

;

Let vNbMonthsToLetAsItIs=6;

let vNbOfMonthsToTakeInAvg=6;

Temp_Data1:

Month("BOOKING DATE") as Period,

Cost

Resident Excel;

// Rest of script

Temp_Data2:

recno() as PeriodID,

Period,

Cost

resident Temp_Data1

order by Cost;

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;

• ###### Re: Calculate Rolling Average for 2 years data

You did not post your error message. Also I don't understand how you will link this final table to your other tables.

For the error : maybe it is because you should use a "group by" on the the first part to aggregate on period, as I expected the data was already agregated with your first post.

///////////////////////////////////////////so replace this :

Temp_Data1:

Month("BOOKING DATE") as Period,

Cost

Resident Excel;

////////////////////////////////////////////////with this:

Temp_Data_00

Month("BOOKING DATE") as Period,

Cost

Resident Excel;

noconcatenate

Temp_Data1: