topic Cumulative Summation by Group in Discussions

Cumulative Summation by Group

Wed, 10 Jun 2009 16:00:13 GMT

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

Wed, 10 Jun 2009 17:09:56 GMT

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

Wed, 10 Jun 2009 18:06:24 GMT

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

Mon, 22 Jun 2009 17:12:20 GMT

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.