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!
¡Resuelto! Ir a solución.
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 🙂
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
Thank you both @atcodedog05 and MichaelSu for both suggestions, as both of them adapt to what I am seeking for.
Happy to help 🙂 @Pablo-GP