BookmarkSubscribeRSS Feed
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.

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.

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.