Cumulative summary tool

Question

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:

ItemDayTotal112124136148151016121714

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:

ItemDayTotalDay Group112212421365148515105161210171410

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

ItemDay GroupTotal126152411026

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:

ItemDay GroupTotal126153211058

How can this be done?

Thanks in advance!

Claje · Accepted Answer

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 Answer

@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