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!
Solved! Go to 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 🙂
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