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.
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”.
if you want to say that after executing above code in Column Expressions more nodes are needed to add flag column then you are right. Was just giving my solution for calculating cumulative sum with threshold. You are interested in flag column or?
are you and @mauuuuu5 together working on this one or you just have similar request? Anyways in case you need to start sum from zero when you go over 20.000 then it is even simpler:
var sum;
if(sum==null || sum > 20000){sum=0;}
sum = sum + column("AMOUNT");
To add flag column use Rule Engine node based on newly added column
to my knowledge if function in this node works as if function works in general - performs different actions based on conditions. Column Expressions node is based on JavaScript so you can take a look on syntax and a bit of explanation here: https://www.w3schools.com/js/js_if_else.asp
think there is one more thing I should explain regarding this if condition a bit more. Code from Column Expressions is executed as whole for each row. So if you would have code without condition sum==null
var sum = 0; // initialization must be done
if(sum > 20000){sum=0;}
sum = sum + column(“AMOUNT”);
the sum variable would never be remembered cause initialization is done for each row. With sum==null you actually do initialization for first row and then you only reset it when sum is higher than 20.000.