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

[Pablo-GP]

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!

[atcodedog05 · Accepted solution]

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 solution]

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

[Pablo-GP]

Thank you both  @atcodedog05  and  MichaelSu for both suggestions, as both of them adapt to what I am seeking for.

[atcodedog05]

Happy to help 🙂 @Pablo-GP