4 Replies Latest reply: Sep 12, 2018 2:07 PM by Suhasini Metri RSS

    Cumulative Sum

    Suhasini Metri

      Hi Guru's,

       

      I Calculated Cumulative_Sum in UI by using Range Sum,

      but i am not getting how  to achieve the same in Script level.

       

      Please help me to achieve this.

       

      Please find  below sample data.

       

          

      YearMonthSum_SalesCumulative sum
      2017Apr120220
      2017Mar100100
      2018Feb100200
      2018Jan100100

       

      Thanks and Regards

      Suhasini G Metri

        • Re: Cumulative Sum
          Giovanne Bergstein

          Hi, try this

           

          tmp:

          LOAD * Inline [

          Year, Month, Sum_Sales

          2017, Apr, 120

          2017, Mar, 100

          2018, Feb, 100

          2018, Jan, 100

          ];

           

           

          tmp1:

          LOAD

          *,

          if(Month =  'Jan', 1,

          if(Month =  'Feb', 2,

          if(Month =  'Mar', 3,

          if(Month =  'Apr', 4,

          if(Month =  'Mai', 5,

          if(Month =  'Jun', 6,

          if(Month =  'Jul', 7,

          if(Month =  'Ago', 8,

          if(Month =  'Set', 9,

          if(Month =  'Out', 10,

          if(Month =  'Nov', 11,

          if(Month =  'Dez', 12 )))))))))))) as MonthNum

          Resident tmp;

           

           

          DROP Table tmp;

           

           

          tmp2:

          LOAD

          *,

          if(Year = Peek(Year), Sum_Sales + Peek(Sum_Sales), Sum_Sales) as Acm_Sales;

          NoConcatenate

          LOAD

          *

          Resident tmp1

          Order by Year, MonthNum;

           

           

          DROP Field MonthNum From tmp2;

           

           

          DROP Table tmp1;

          • Re: Cumulative Sum
            Quy Nguyen

            Try this:

            A:
            Load Year, Month, Date#(Year&'-'&Month, 'YYYY-MMM') As YearMonth, Sum_Sales;
            LOAD * INLINE [
            Year, Month, Sum_Sales
            2017, May, 200
            2017, Apr, 120
            2017, Mar, 100
            2018, Mar, 50
            2018, Feb, 100
            2018, Jan, 100];
            Final:
            Load Year, Month, Sum_Sales,     
             If(Peek(Year) <> Year,  Sum_Sales, Peek("Cumulative sum") + Sum_Sales ) As "Cumulative sum";
            Load Year, Month, Sum_Sales , Sum_Sales as "Cumulative sum"
            Resident A
            Order by YearMonth;
            
            
            Drop Table A;