1 Reply Latest reply: Sep 29, 2016 12:59 PM by Sunny Talwar RSS

    monthly-increment value aggregated by dimensions

    Oliver Krause

      Dear all,

      I have a table like this:

       

      SRC:
      LOAD * INLINE [
      Dim1, Dim2, Dim3, Thema1, Thema2, MSNr
      400, G11, BF, 10, 20, 01.16
      400, G11, BF, 15, 152, 02.16
      400, G11, BF, 99, 99, 03.16
      500, G11, BF, 1, 2, 01.16
      500, F10, BF, 30, 21, 02.16
      500, F10, BF, 1, 1, 05.16
      ]
      ;

      and need to get a new column SUM_Thema 1 which sums up

      column Thema1 IF Derivat,ISt,MS are equal - SORTED BY Date

       

      Thanks for your help

      Oliver

         

      DerivatIStMSMSNrThema1Thema2SUM Thema1
      F10500BF01.16302130
      F10500BF02.161131
      G11400BF01.16102010
      G11400BF02.161515225
      G11400BF03.169999124
      G11500BF05.16121
        • Re: monthly-increment value aggregated by dimensions
          Sunny Talwar

          Try this out:

           

          SRC:

          LOAD ISt,

            Derivat,

            MS,

            Thema1,

            Thema2,

            Date#(MSNr&'.2016', 'MM.DD.YYYY') as MSNr

          INLINE [

          ISt, Derivat, MS, Thema1, Thema2, MSNr

          400, G11, BF, 10, 20, 01/16/2016

          400, G11, BF, 15, 152, 02/16/2016

          400, G11, BF, 99, 99, 03/16/2016

          500, G11, BF, 1, 2, 01/16/2016

          500, F10, BF, 30, 21, 02/16/2016

          500, F10, BF, 1, 1, 05/16/2016

          ];

           

          FinalSRC:

          LOAD *,

            If(ISt = Previous(ISt) and Derivat = Previous(Derivat) and MS = Previous(MS), RangeSum(Peek('SUM Thema 1'), Thema1), Thema1) as [SUM Thema 1]

          Resident SRC

          Order By ISt, Derivat, MS, MSNr ASC;

           

          DROP Table SRC;