6 Replies Latest reply: Feb 15, 2016 11:38 PM by sasi k RSS

    Rolling 12 months sum

    arjun rao

      Hi All,

      I want to show rolling 12 months in the following format in a pivot table.

      Sales of Jan-14 is to be shown sum of Sales from  Feb-13 to Jan-14.

      Sales of Feb-14 is to be shown sum of Sales from  Mar-13 to Feb-14.

      and so on ...

      Sales of Jan-16 is to be shown sum of Sales from  Feb-15 to Jan-16.

      Sales of Feb-16 is to be shown sum of Sales from  Mar-15 to Feb-16

      I am able to show rolling 12 months as shown.

      Capture.JPG

       

      Please help me.

      Gysbert Wassenaar

      jagan mohan rao appala

      MAYIL VAHANAN RAMASAMY

      Celambarasan Adhimulam

      Kush141087

      Sunny T

      swuehl

      Script:

      Sales:

      EmpSales:

      load *,  MonthEnd(date([Transaction Date],'YYYYMM')) as Period1, MonthName([Transaction Date]) as Month;

      load *,MonthEnd(date#(MonthName&'-'&Year,'MMM-YYYY')) as [Transaction Date];

      Load * inline

      [Year,MonthName,Product,Sales

      2014,Jan,E1,1

      2014,Feb,E1,2

      2014,Mar,E1,6

      2014,Apr,E1,7

      2014,May,E1,8

      2014,Jun,E1,9

      2014,Jul,E1,10

      2014,Aug,E1,1

      2014,Sep,E1,2

      2014,Oct,E1,3

      2014,Nov,E1,4

      2014,Dec,E1,5

      2015,Jan,E1,6

      2015,Feb,E1,7

      2015,Mar,E1,8

      2015,Apr,E1,9

      2015,May,E1,10

      2015,Jun,E1,1

      2015,Jul,E1,2

      2015,Aug,E1,3

      2015,Sep,E1,4

      2015,Oct,E1,5

      2015,Nov,E1,6

      2015,Dec,E1,7

      2016,Jan,E1,6

      2016,Feb,E1,7

      2014,Jan,E2,9

      2014,Feb,E2,10

      2014,Mar,E2,4

      2014,Apr,E2,5

      2014,May,E2,6

      2014,Jun,E2,7

      2014,Jul,E2,8

      2014,Aug,E2,9

      2014,Sep,E2,3

      2014,Oct,E2,4

      2014,Nov,E2,5

      2014,Dec,E2,6

      2015,Jan,E2,7

      2015,Feb,E2,8

      2015,Mar,E2,9

      2015,Apr,E2,10

      2015,May,E2,1

      2015,Jun,E2,2

      2015,Jul,E2,3

      2015,Aug,E2,4

      2015,Sep,E2,5

      2015,Oct,E2,9

      2015,Nov,E2,10

      2015,Dec,E2,1

      2016,Jan,E2,2

      2016,Feb,E2,3];

      PeriodTbl:

      LOAD date(fieldvalue('Period1',recno()),'YYYYMM') as Period

      AUTOGENERATE fieldvaluecount('Period1');

      AsOfPeriodTable:

      LOAD

      Period as AsOfPeriod

      ,'Current' as PeriodType

      ,Period as Period1

      ,Year(Period) as Year

      RESIDENT PeriodTbl;

      CONCATENATE (AsOfPeriodTable)

      LOAD

      Period as AsOfPeriod

      ,'Rolling 12' as PeriodType

      ,date(addmonths(Period,1-iterno()),'YYYYMM') as Period1

      ,Year(Period) as Year

      RESIDENT PeriodTbl

      WHILE iterno() <= 12;

      DROP TABLE PeriodTbl;

        • Re: Rolling 12 months sum
          Sunny Talwar

          May be this:

           

          EmpSales:

          LOAD *,

            MonthEnd(Date([Transaction Date],'YYYYMM')) as Period1,

            MonthName([Transaction Date]) as Month;

          LOAD *,

            MonthEnd(Date#(MonthName&'-'&Year,'MMM-YYYY')) as [Transaction Date];

          LOAD * Inline [

          Year,MonthName,Product,Sales

          2014,Jan,E1,1

          2014,Feb,E1,2

          2014,Mar,E1,6

          2014,Apr,E1,7

          2014,May,E1,8

          2014,Jun,E1,9

          2014,Jul,E1,10

          2014,Aug,E1,1

          2014,Sep,E1,2

          2014,Oct,E1,3

          2014,Nov,E1,4

          2014,Dec,E1,5

          2015,Jan,E1,6

          2015,Feb,E1,7

          2015,Mar,E1,8

          2015,Apr,E1,9

          2015,May,E1,10

          2015,Jun,E1,1

          2015,Jul,E1,2

          2015,Aug,E1,3

          2015,Sep,E1,4

          2015,Oct,E1,5

          2015,Nov,E1,6

          2015,Dec,E1,7

          2016,Jan,E1,6

          2016,Feb,E1,7

          2014,Jan,E2,9

          2014,Feb,E2,10

          2014,Mar,E2,4

          2014,Apr,E2,5

          2014,May,E2,6

          2014,Jun,E2,7

          2014,Jul,E2,8

          2014,Aug,E2,9

          2014,Sep,E2,3

          2014,Oct,E2,4

          2014,Nov,E2,5

          2014,Dec,E2,6

          2015,Jan,E2,7

          2015,Feb,E2,8

          2015,Mar,E2,9

          2015,Apr,E2,10

          2015,May,E2,1

          2015,Jun,E2,2

          2015,Jul,E2,3

          2015,Aug,E2,4

          2015,Sep,E2,5

          2015,Oct,E2,9

          2015,Nov,E2,10

          2015,Dec,E2,1

          2016,Jan,E2,2

          2016,Feb,E2,3

          ];

           

          PeriodTbl:

          LOAD Date(FieldValue('Period1',RecNo()),'YYYYMM') as Period

          AutoGenerate FieldValueCount('Period1');

           

          AsOfPeriodTable:

          LOAD Period as AsOfPeriod,

            'Current' as PeriodType,

            Period as Period1

          // Year(Period) as Year

          Resident PeriodTbl;

           

          Concatenate (AsOfPeriodTable)

          LOAD Period as AsOfPeriod,

            'Rolling 12' as PeriodType,

            Date(MonthEnd(AddMonths(Period, 1-IterNo())), 'YYYYMM') as Period1

          // Year(Period) as Year

          Resident PeriodTbl

          While IterNo() <= 12;

           

          DROP Table PeriodTbl;


          Capture.PNG

            • Re: Rolling 12 months sum
              arjun rao

              Hi Sunny T

              Thank You for solution. All the data is matching for 12 months rolling.

              But when I select Year and Month, it is showing Current Sales.

              Please advise.

                • Re: Rolling 12 months sum
                  jagan mohan rao appala

                  Hi,

                   

                  Try this script

                   

                  EmpSales:

                  LOAD *,

                    MonthEnd(Date([Transaction Date],'YYYYMM')) as Period1,

                    MonthName([Transaction Date]) as Month;

                  LOAD *,

                    MonthEnd(Date#(MonthName&'-'&Year,'MMM-YYYY')) as [Transaction Date];

                  LOAD * Inline [

                  Year,MonthName,Product,Sales

                  2014,Jan,E1,1

                  2014,Feb,E1,2

                  2014,Mar,E1,6

                  2014,Apr,E1,7

                  2014,May,E1,8

                  2014,Jun,E1,9

                  2014,Jul,E1,10

                  2014,Aug,E1,1

                  2014,Sep,E1,2

                  2014,Oct,E1,3

                  2014,Nov,E1,4

                  2014,Dec,E1,5

                  2015,Jan,E1,6

                  2015,Feb,E1,7

                  2015,Mar,E1,8

                  2015,Apr,E1,9

                  2015,May,E1,10

                  2015,Jun,E1,1

                  2015,Jul,E1,2

                  2015,Aug,E1,3

                  2015,Sep,E1,4

                  2015,Oct,E1,5

                  2015,Nov,E1,6

                  2015,Dec,E1,7

                  2016,Jan,E1,6

                  2016,Feb,E1,7

                  2014,Jan,E2,9

                  2014,Feb,E2,10

                  2014,Mar,E2,4

                  2014,Apr,E2,5

                  2014,May,E2,6

                  2014,Jun,E2,7

                  2014,Jul,E2,8

                  2014,Aug,E2,9

                  2014,Sep,E2,3

                  2014,Oct,E2,4

                  2014,Nov,E2,5

                  2014,Dec,E2,6

                  2015,Jan,E2,7

                  2015,Feb,E2,8

                  2015,Mar,E2,9

                  2015,Apr,E2,10

                  2015,May,E2,1

                  2015,Jun,E2,2

                  2015,Jul,E2,3

                  2015,Aug,E2,4

                  2015,Sep,E2,5

                  2015,Oct,E2,9

                  2015,Nov,E2,10

                  2015,Dec,E2,1

                  2016,Jan,E2,2

                  2016,Feb,E2,3

                  ];

                   

                   

                  PeriodTbl:

                  LOAD Date(FieldValue('Period1',RecNo()),'YYYYMM') as Period

                  AutoGenerate FieldValueCount('Period1');

                   

                   

                  AsOfPeriodTable:

                  LOAD Period as AsOfPeriod,

                    Year(Period) AS AsOfYear,

                    Month(Period) AS AsOfMonth,

                    'Current' as PeriodType,

                    Period as Period1

                  // Year(Period) as Year

                  Resident PeriodTbl;

                   

                   

                  Concatenate (AsOfPeriodTable)

                  LOAD Period as AsOfPeriod,

                  Year(Period) AS AsOfYear,

                    Month(Period) AS AsOfMonth,

                    'Rolling 12' as PeriodType,

                    Date(MonthEnd(AddMonths(Period, 1-IterNo())), 'YYYYMM') as Period1

                  // Year(Period) as Year

                  Resident PeriodTbl

                  While IterNo() <= 12;

                   

                   

                  DROP Table PeriodTbl;

                   

                  Regards,

                  Jagan.

              • Re: Rolling 12 months sum
                sasi k

                hI,

                Pls find the attachment