Running Sum and with restart

Hi,

I would have thought that many users would like to do what I want to do but I have not been able to find anyone providing a solution to my problem and I would therefore appreciate if I could get some help.

I want to create a cumulattive sum for the data in one column where the cumulative sum needs to restart based on data in another column, eg:

Col1           Col2            Cumulative Sum

2                    A                  2

4                    A                  6

3                    A                  9

4                    B                  4

3                    B                  7

4                    C                  4

.....

Any help will be very much appreciated

1 Like

HI Mortsrah

You can do this with a combination of the Group Loop and the Moving Aggregation nodes

First sort the input data by the data you will be restarting, Col2 in your case. 

Next pass the data to a Group  Loop start, picking Col2 to group by. Then for each group, pass to a Moving Aggregation node, picking Col1 to aggregate over, checking the Cumulative computation box, and selection Sum as the aggregation method. Close with a Loop End.

I've attached an example for your case.

David

 

2 Likes

Thank you very much David,

Your solution works great. I really appreciate your help

 

Thank you!. Your example helped me today!
Gerard

2 Likes

:slight_smile:

1 Like

Hi David, Thank you sooo much for the above running sum solution. It was very very helpful in what I’m doing.

2 Likes

Hi there,

I find the above solution very interesting and was trying to use in one my workflow but running into roadblock. Trying to get cumulative sum of one the double column. Here is my workflow. Any help

Java_loop_Cumulative_Sum.knwf (17.1 KB)

Hello @kamleshp,

I have taken a look at your workflow and have seen you have 4 CSV files in your data folder but each has only 1 row. Do you want cumulative sum for each file and this is just example where you have one row per file? Do you have different groups in each file for which you want cumulative sum? From experience providing input data (dummy works just fine) and desired output usually is the best way to get help/suggestion/solution :wink:

Br,
Ivan

1 Like