cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

Cumulative Summation by Group

I am trying to do a Cumulative Summation by a group value in another column, and so far nothing I have tried (For, While, double Summation) has worked. I can do a cumulative sum for the entire table with:

Summation( ::i = 1, Row(), :Total WIP[::i, Empty()] )

However, I need to do a separate cumulative sum for each "segment" I have in another column (i.e. I need a cumulative sum where :seg==1, and then a separate one where :seg==2, however I can't get the index to re-set itself when the seg value changes)

Any help would be greatly appreciated.
3 REPLIES 3

Re: Cumulative Summation by Group

There is an easy way to do it. Let me explain my data table first:

-There is a column specifying the group, called "Seg".
-There is the data column, called "Y".
-The column containing the cumulative sum formula is called "Cum Sum".

The formula in the Cum Sum column is:

If( Row() == 1, :Y,
:Seg[Row()] == :Seg[Row() - 1], :Cum Sum[Row() - 1] + :Y[Row()],
:Seg[Row()] != :Seg[Row() - 1], :Y[Row()]
)

This formula only works if the data table is sorted by seg, and the Y column is in the order you want.

Re: Cumulative Summation by Group

Hi Jonathan,

Thank you, that gets me a whole lot closer, and while it does partially re-set where Seg changes, the cumulative sum itself doesn't reset still?

My table is sorted by Seg and the Y column (Total WIP) is in the order I want it in.

If(
Row() == 1, :Total WIP,
:Seg[Row()] == :Seg[Row() - 1],
:Cum Sum[Row() - 1] + :Total WIP[Row()],
:Seg[Row()] != :Seg[Row() - 1], :Total WIP[Row()]
)

Re: Cumulative Summation by Group

I'm glad the example helped you a little. But, I am confused as to what you are trying to do. The example I provided does indeed do the cumulative sum, with the cumulative sum reseting when the grouping changes. I thought that is what you wanted to do. Please describe in more detail what you want to accomplish.