Add cumulative sum until reaching a Threshold and flag such rows
Dear Knimers,
I am building a workflow to add two columns based on the following conditions:
Threshold: I want to calculate the cumulative sum of the column Amount per person, but such cumulative sum has to reset each time before reaching a Threshold let say 20.000.
For instance the person with ID =10 has the following records 4.120 +5.382 + 6.613 = 16.115 and with the first three reaches a cumulative sum before the reaching 20.000. For instance if I add the fourth record = 6.094 the cumulative sum surpass the 20.000 threshold 4.120 +5.382 + 6.613 + 6.094 = 22.209 hence the calculation must be done with three records and not with four
So the column has to reset and continue calculating the cumulative sum for the the next records.
The second column should flag the last record before reaching the threshold, which in the example is the third record 4.120 +5.382 + 6.613 = 16.115,. And the same must be done with the next columns.
Please see the image
I have done a workflow (is attached), but I am unable to make a nested loop as I guess is the solution. I started with the “Group Loop Start” and tried to control the threshold with “Variable Condition Loop End”.
Thank you