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

Subtotal.knwf (87.5 KB)

Hi @mauuuuu5,

Here’s something quick that I came up with:

Let me know what you think.

3 Likes

Thank you brilliant, highly appreciated

1 Like

Hi there @mauuuuu5,

if you are ok with scripting you can use following code in Column Expressions node to calculate cumulative sum with Threshold :wink:

var sum;
if(sum==null){sum=0;}

sum = sum + column("AMOUNT");

if(sum < 20000){
    sum;
}
   else{
sum=column("AMOUNT");
}

Br,
Ivan

4 Likes

Hello @Ipazina,

There is some way to obtain the result according to the attached image.

Thank you,

Hi there @gcartagena,

welcome to KNIME Community!

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?

Br,
Ivan

Thank you very much @Ipazina,

I apologize for not clarifying my point.

The above code generates the following result: 6,622 + 4,828 + 6,309 = 17,759.

However, I need to generate the following: 6,622 + 4,828 + 6,309 + 4,954 = 22,713, because the threshold is > = 20,000.

Attached image of the previous code and the expected.

.

Thank you.

1 Like

Hi there @gcartagena,

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 :wink:

Br,
Ivan

Excellent @pazina, Thank you.

Answering your query. Indeed @mauuuuu5 I consider him as my mentor.

ÂżCan you recommend reading some text, I need to understand how the If function works in this node?

1 Like

Hi @gcartagena,

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

Br,
Ivan

Perfect, thank you very much.

Hi @gcartagena,

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.

Hope this makes sense :slight_smile:

Br,
Ivan

1 Like

Hello @ipazina,

Excellent thank you very much for the explanation. I already have it clearer.

regards,

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.