Cumulative summary tool

[PaulMRichard]

Hi All,

My questions has to do with how one could achieve a cumulative summary based on one of the 'group by' columns. Here's what I mean:

Let's say I have the following data:

Item	Day	Total
1	1	2
1	2	4
1	3	6
1	4	8
1	5	10
1	6	12
1	7	14

I added the following column to group the 'day' column into ranges. So any day within the first two days is in the '2' group. Any day within the first 5 days is int he '5' group, and any day within the first 10 days is in the '10' group, like so:

Item	Day	Total	Day Group
1	1	2	2
1	2	4	2
1	3	6	5
1	4	8	5
1	5	10	5
1	6	12	10
1	7	14	10

Then I summarized the table based on the Item and Day group columns, taking the sum of the 'total' column:

Item	Day Group	Total
1	2	6
1	5	24
1	10	26

However, what I want is for my totals to include to sums from the prior day group. In other words, the total for the '5' day group should include the total from the '1' group, and the total from the '10' group should include the totals from both '1' and '5', like so:

Item	Day Group	Total
1	2	6
1	5	32
1	10	58

How can this be done?

Thanks in advance!

[Claje · Accepted solution]

Probably the easiest way to do this is with the Running Total tool.  You can Group by your product and create a Running Total of your Total column, and it will add in the order it receives the data, creating a new column.  Then, a Select tool can be used to deselect the old total and rename the RunTotal_ column.

You could also do this with a Multi-Row Formula, but this configuration is a little more complex (although more powerful), so I'd suggest trying out Running Total first.

[PaulMRichard · Accepted solution]

@Claje thanks for the reply. This worked for me, with one additional step. After the running total is compiled, I summarized the result using the maximum value in the running total column. 

Workflow attached