Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Cumulative Summation by Group

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Cumulative Summation by Group

Jun 10, 2009 9:00 AM
(1779 views)

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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()]

)

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()]

)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.