Recursive Lag with math formula?

[Danny8m8]

Hi,
I am having a hard time trying to figure out the following:

So basically what i want it to do is calculate the volume_mt per row by doing “previous volume_mt - con_prd_vol_mt”.

Using the LAG node obviously only gives me -100, as the rest has not been calculated yet.
I’ve tried to use a recursive loop, as I need the newly calculated value as input for my next row. However I still did not manage to get it working. Anyone that could help me out?

[ArjenEX]

Hi @Danny8m8

Welcome to the KNIME community!
You can solve this in a several ways but since loops usually are quite resource intensive (depending on the size and complexity of the dataset) I’d like to stay away from them as much as possible.

A way to solve this is via the Moving Aggregation node:

Here, I first apply the start volume to all rows via the Missing Value node.

Next, with the Moving Aggregation node, you can create a moving sum of con_prd_vol_mt. This to be used later on to substract it from the earlier defined starting volume.

With the moving sum determined, a simple math calculation in the associated node to update the volume_mt should do the trick.

Note: use the Convert to Int option if you want to keep the values as integer.

WF:
Moving aggregation - volume calculation.knwf (26.8 KB)

Hope this helps!

[Danny8m8]

Thanks Arjen, that worked like a charm!

Still had to use a Group loop in my case as all the steps have to be applied for each customer (defined by 4 columns) separately. But that was a lot more straight forward than the recursive loop.
Thanks again