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]

InvDateMapping:

mapping load

PNR,

min("InvDt") as MinInvDate

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

(qvd)

Group by PNR;

Excel:

Load

    *,

    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:

Mapping Load distinct

    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:

load

  *,

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

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

    Resident ExcelHisCalc;

Drop Table ExcelHisCalc;

DaysAdvNC:

// load *,

// '14 Days Advanced Purchase' as Policy,

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

Load Distinct

  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:

LOAD

    Month("BOOKING DATE") as Period,

    Cost

    Resident Excel;

// Rest of script

Temp_Data2:

LOAD

recno() as PeriodID,

Period,

Cost

resident Temp_Data1

order by Cost;

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;

[thomaslg_wq]

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:

LOAD

    Month("BOOKING DATE") as Period,

    Cost

    Resident Excel;

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

Temp_Data_00

LOAD

    Month("BOOKING DATE") as Period,

    Cost

       Resident Excel;

noconcatenate

Temp_Data1:

LOAD

    Period,

   sum(Cost) as Cost

       Resident Temp_Data_00

group by Period;

Drop table Temp_Data_00;