11 Replies Latest reply: Nov 29, 2016 3:53 AM by Thomas Le Gall RSS

    Calculate Rolling Average for 2 years data

    RAM MUTHIAH M

      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
          RAM MUTHIAH M

          I want this in Script, not in Edit expression

          • Re: Calculate Rolling Average for 2 years data
            Thomas Le Gall

            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;

              • Re: Calculate Rolling Average for 2 years data
                RAM MUTHIAH M

                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
                    Thomas Le Gall

                    Could you post your code ?

                    And a screenshot of the error ?

                      • Re: Calculate Rolling Average for 2 years data
                        RAM MUTHIAH M

                        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;

                          • Re: Calculate Rolling Average for 2 years data
                            Thomas Le Gall

                            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;