6 Replies Latest reply: Feb 10, 2017 1:45 AM by nayan lalla RSS

    Converting Cumulative values to Monthly values

    nayan lalla

      Hi

       

      I have cumulative sales data information per month. The sales accumulates for 12 months in a year, then starts again from zero in the next year and so on.   I want to convert them to actual sales per month (in the back end script).  Please can you assist.

       

      Below  is a sample of the cumulative sales data.

       

      Thanking you in advance.

       

      Kind regards

      Nayan

       

         

      YearMonthCumulative Sales
      2011Jan10 662 344
      2011Feb24 839 013
      2011Mar40 622 392
      2011Apr52 416 659
      2011May66 363 331
      2011Jun82 017 482
      2011Jul98 197 014
      2011Aug113 096 120
      2011Sep132 661 541
      2011Oct151 398 421
      2011Nov174 945 478
      2011Dec196 504 075
      2012Jan12 983 644
      2012Feb27 480 251
      2012Mar44 075 819
      2012Apr59 791 319
      2012May79 100 267
      2012Jun96 175 752
      2012Jul113 595 794
      2012Aug131 934 528
      2012Sep147 445 684
      2012Oct166 271 525
      2012Nov184 102 390
      2012Dec202 629 125
      2013Jan11 806 832
      2013Feb26 535 471
      2013Mar40 757 925
      2013Apr56 447 051
      2013May73 622 470
      2013Jun88 894 199
      2013Jul107 485 214
      2013Aug124 977 463
      2013Sep139 171 251
      2013Oct158 939 728
      2013Nov176 933 676
      2013Dec186 725 594
        • Re: Converting Cumulative values to Monthly values
          Sunny Talwar

          May be like this:

           

          Table:

          LOAD Year,

            Month(Date#(Month, 'MMM')) as Month,

            [Cumulative Sales];

          LOAD * INLINE [

              Year, Month, Cumulative Sales

              2011, Jan, 10 662 344

              2011, Feb, 24 839 013

              2011, Mar, 40 622 392

              2011, Apr, 52 416 659

              2011, May, 66 363 331

              2011, Jun, 82 017 482

              2011, Jul, 98 197 014

              2011, Aug, 113 096 120

              2011, Sep, 132 661 541

              2011, Oct, 151 398 421

              2011, Nov, 174 945 478

              2011, Dec, 196 504 075

              2012, Jan, 12 983 644

              2012, Feb, 27 480 251

              2012, Mar, 44 075 819

              2012, Apr, 59 791 319

              2012, May, 79 100 267

              2012, Jun, 96 175 752

              2012, Jul, 113 595 794

              2012, Aug, 131 934 528

              2012, Sep, 147 445 684

              2012, Oct, 166 271 525

              2012, Nov, 184 102 390

              2012, Dec, 202 629 125

              2013, Jan, 11 806 832

              2013, Feb, 26 535 471

              2013, Mar, 40 757 925

              2013, Apr, 56 447 051

              2013, May, 73 622 470

              2013, Jun, 88 894 199

              2013, Jul, 107 485 214

              2013, Aug, 124 977 463

              2013, Sep, 139 171 251

              2013, Oct, 158 939 728

              2013, Nov, 176 933 676

              2013, Dec, 186 725 594

          ];

           

          FinalTable:

          LOAD *,

            If(Year = Previous(Year), RangeSum([Cumulative Sales], -Previous([Cumulative Sales])), [Cumulative Sales]) as Sales

          Resident Table

          Order By Year, Month;

           

          DROP Table Table;

          • Re: Converting Cumulative values to Monthly values
            Jonathan Dienst

            You could use a script like this:

             

            LOAD Year,

              Month,

              If(Month = 1,

              Sales,

              Sales - Previous(Sales)

              ) as Sales

            ;

            LOAD Year,

              Month(Date#(Month, 'MMM')) as Month,

              [Cumulative Sales] as Sales

            Resident DataTable

            ORDER BY Year, Date#(Month, 'MMM');


            If your data source is sorted or sortable, you load directly from the source, rather than a resident load. This also assumes that the Month field is a text field. This needs to be converted to a dual value and that is how the Month field is loaded.