Aggregate Amounts if the individual rows (Dates or Record Criteria) are Consecutive

Question

Hi All,

This is my first open question on this amazing community. I have seen a lot of proactiveness and achieve amazing progress thanks to the people's contribution in the forum.

I have a new challenge that hopefully some of you will be able to help to resolve.

The request looks simple, but I have not been able to achieve it. I have been trying to use the Multi-Row Formula, but I haven't been able to do resolve it yet.

Basically, I have a dataset with Dates and Amounts, there are some gaps between the dates and I want to accumulate the amounts if the dates are consecutive.

This is how the input data looks like

The goal is to obtain something like this

Thanks!

OUTPUT Consecutive Days Aggregation.xlsx

INPUT Consecutive Days Aggregation.xlsx

atcodedog05 · Accepted Answer

Hi @Pablo-GP

Here is how you can do it

Workflow:

1. Using multi-row formula tool if difference between previous row day and current row day is 1 then take above row group (consecutive dates same group) if not start new group+1. This way I am grouping consecutive days.

2. Using summarize tool to groupby group and sum of amount.

4. Using join tool to join group agg amt with individual rows.

Hope this helps 🙂

MichaelSu · Accepted Answer

Hi @Pablo-GP ,

I like @atcodedog05's solution above. Additionally, you can consider leveraging the Running Total tool in order to calculate cumulative sums. See attached.

Thanks,

Mike