7 Replies Latest reply: Sep 30, 2016 3:49 AM by Ganesh Bagavathi RSS

    Rolling Sum/Rolling Average at Script

    Ganesh Bagavathi

      Hello,

       

      I am looking for the functionality to introduce a column for Rolling Sum or Rolling Average in the attached Excel. It is day wise data and the rolling sum has to be reset at the end of every month.

      Please give me your suggestions.

       

      Thanks,

        • Re: Rolling Sum/Rolling Average at Script
          Rupam Das

          Something like this

          RollingMonths:

          load Period, Period as RollingPeriod, year(Period) as RollingYear Resident Data1;

          load Period, Addmonths(Period,1,1) as RollingPeriod, year(Addmonths(Period,1,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,2,1) as RollingPeriod, year(Addmonths(Period,2,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,3,1) as RollingPeriod, year(Addmonths(Period,3,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,4,1) as RollingPeriod, year(Addmonths(Period,4,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,5,1) as RollingPeriod, year(Addmonths(Period,5,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,6,1) as RollingPeriod, year(Addmonths(Period,6,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,7,1) as RollingPeriod, year(Addmonths(Period,7,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,8,1) as RollingPeriod, year(Addmonths(Period,8,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,9,1) as RollingPeriod, year(Addmonths(Period,9,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,10,1) as RollingPeriod, year(Addmonths(Period,10,1)) as RollingYear Resident Data1;

          load Period, Addmonths(Period,11,1) as RollingPeriod, year(Addmonths(Period,11,1)) as RollingYear Resident Data1;

          • Re: Rolling Sum/Rolling Average at Script
            Sunny Talwar

            May be this?

             

            Table:

            LOAD CCY_EXCH_VALID_DT,

              MonthName(CCY_EXCH_VALID_DT) as MonthYear,

                FROM_CCY_CD,

                TO_CCY_CD,

                RATE_AMT

            FROM

            [Rolling Sum.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

            FinalTable:

            LOAD *,

              If(FROM_CCY_CD = Previous(FROM_CCY_CD) and MonthYear = Previous(MonthYear), RangeAvg(Peek('RangeAvg'), RATE_AMT), RATE_AMT) as RangeAvg,

              If(FROM_CCY_CD = Previous(FROM_CCY_CD) and MonthYear = Previous(MonthYear), RangeSum(Peek('RangeSum'), RATE_AMT), RATE_AMT) as RangeSum

            Resident Table

            Order By FROM_CCY_CD, CCY_EXCH_VALID_DT;

             

            DROP Table Table;


            Capture.PNG