
Cummulative Sum
joey May 15, 2014 10:03 AM (in response to Rusch Mauzy)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.