# Cummulative Sum

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.

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

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