recursive loop for creating cumulative sum

[Homa]

I want to build the following loop :
input: 1. monthly sales values columns (sales 01-2018, sales 02-2018, …, sales 12-2024) 2. conract value
output: these monthly columns: “contract value - sales 01-2018”, “contract value - (sales 01-2018+sales 02-2018)”, “contract value - (sales 01-2018+sales 02-2018+sales 03-2018)”, …, “contract value - (sales 01-2018+sales 02-2018+ … +sales 12-2024)”
I have already created the monthly sales columns using a loop, but since I need a cumulative sum of the previous iterations of this loop, I guess it is not possible to add an expression to my previous node. Now I am trying to use a recursive loop to get the cumulative sum of the previously created sales reports. Any suggestion on how I should proceed?

[MartinDDDD]

Hey,

Mind putting a simple example of input and output into Excel and uploading it here?

From what I read I am sure that this is a problem that can be solved in KNIME and one option for sure might be recursion.

[Homa]

sure. my input and output data is attached.
input and output table.xlsx (13.2 KB)

[Homa]

my simple workflow is also attached.
recursive loop for acreating cumulative sum.knwf (81.4 KB)

[MartinDDDD]

Thanks a lot for providing the data - makes it a lot easier to help you.

// Edit:

I found a way to do this without using any recursive loop which should be really good for performance. I keep my explanations below and have also kept the initial solution approach in the workflow - might come in handy for someone at some stage :-).

So here is the v2 prototype:
recursive loop for acreating cumulative sumv2.knwf (253.3 KB)

Overview:

Most of it is the same with the exception that the complex recursive part is replaced by Moving Aggregator node (this calculates the aggregate sum of the sale column) followed by a math formula node that creates a new column “New Contract Value” which is calculated as Contract value - Cumultative sum of sales.

Old / initial approach and explanation
So what you did was sending all data straight both to output and to the bottom port that goes back to the beginning…

With recursive loops what you typically want to do is to do something with part of the data and send it to the top port of recursive loop end, send the unprocessed portion back to the beginning etc…

I’ve build a working prototype:
recursive loop for acreating cumulative sum.knwf (206.9 KB)

Overview:

As you can see there’s a lot more going on to get to the desired result - e.g. unpivoting all date columns, turning dates in string format to date format, initiating a group loop to iterate over each rowID (I assume that each row relates to an individual contract) and then nested in that group loop is the recursive loop.

Inside this loop always the top row is split off, new contract value is calculated as contract value - sales. Then we send that “processed” row to the top port of recursive loop end, where it is collected. We also turn that row into a variable and then set the contract value of the remaining data (botton port of row splitter node) to this new contract value… with the new contract value inserted it goes to the bottom port of recursive loop end, which is sent back to the beginning of the loop… then the next row is split off, processed etc… until all rows have been processed.

Loops in general and especially recursive loops can turn quite slot with growing data sets - there’s a fair bit of space to optimize - e.g. once contract value is 0 the loop could be stopped early etc… so some ideas for you to look into.

[Homa]

Thanks very much @MartinDDDD. It worked perfectly.