1 Reply Latest reply: May 15, 2014 10:03 AM by joey RSS

    Cummulative Sum

    Rusch Mauzy

      I want to "restart" the cummulative sum for the measure Over/(Short), at each subtotal. That is, to restart the accumulation at part number 101.

       

      Please help.

       

      Thank you.

       

      Rusch

       

      100 2009-03-02 585 585

      100 2009-03-03 36 621

      100 2009-03-04 -19 602

      100 2009-03-05 434 1036

      100 2009-03-06 523 1559

      100 2009-03-07 -1 1558

      100 2009-03-09 370 1928

      100 2009-03-10 684 2612

      100 2009-03-11 -3 2609

      100 2009-03-12 432 3041

      100 2009-03-13 -45 2996

      100 2009-03-14 481 3477

      100 2009-03-16 161 3638

      100 2009-03-17 676 4314

      100 2009-03-18 10 4324

      100 2009-03-19 124 4448

      100 2009-03-20 721 5169

      100 2009-03-21 -1 5168

      100 2009-03-23 270 5438

      100 2009-03-24 798 6236

      100 2009-03-25 -92 6144

      100 2009-03-26 212 6356

      100 2009-03-27 -60 6296

      100 2009-03-28 750 7046

      100 2009-03-30  7046

      Subtotal  7046 7046

      101 2009-03-02 -2302 4744

      101 2009-03-03 -312 4432

      101 2009-03-04 12 4444

      101 2009-03-05 -1768 2676

      101 2009-03-06 -2243 433

      101 2009-03-07 498 931

      101 2009-03-08  931

      101 2009-03-09 -1454 -523

      101 2009-03-10 -3162 -3685

      /code

        • Cummulative Sum
          joey

          Since no one's taken a stab I'll throw out my first thought:

           

          This uses the external lookup which is the pro version only.  It also assumes your incoming report is sorted by part number (though you could work arround that if you had to).

           

          On your current model, add a calculated field for the RecNo function.

           

          You'll need two summaries in this model.  The first will have a key of the record number, and keep a cumulative sum as the measure field.  The second will have a key of part number, and the measure column will be min(RowNu).  Export these two summaries.  You'll also want to output the table.

           

          In a new model, use the table as input. You'll need two external lookups.  One will use part number and look up your second export to return the first row number that part appeared in.  Create a calculated field contain one less than that number.  For example, part 101 would list 27 as the first row number it appears in.  You would create a calculated filed that has 26 in it for part 101.  Note that row 26 contains the last part before part 101 begins.

           

          For the second lookup, use the two columns that contain row numbers (26 & 27), and lookup the other export of your first model to import the cumulative sum for each total.  Add a new calculated column that calculates the difference between these two columns (You'll need to handle nulls for the first part number). 

           

          That will be the cumulative sum with the total resetting on each part number change.  There are some limitations to how you can use this field.  It's not the most elegant solution, but it works.

          /FONT