Calculate single month values from Pivot with cumulative values

After closing I transfer account values from our finance application to KNIME. I have used pivot to create a timeline of the data.

Problem is that I only get cumulative values and not monthly ones. But I need for later processing the monthly values, so I somehow have to subtract the columns. As each month a new column is added calculation can’t be done using fixed column names, it must be dependent on column index.

I was already trying with Math Formula Multiple Column, but I failed as I cannot say:
single month 4 = value column index 4 - value column index 3
single month 3 = value column index 3 - value column index 2


Hi @Reinhard-Korn
Your challenge has an easy solution. Please take a view on the workflow that I am sharing.




Good morning @Reinhard-Korn

I just implemented the Loop for an n-limited number of rows.
In the case you downloaded it before. There is already a version2 published.


Dear @gonhaddock ,
implementing a loop was a very good idea as I have to do this for over 800 accounts/lines. I added your solution to my wf and it works perfectly.
Nevertheless I took me some to to fully understand it, but it’s a very good solution.

Thanks for this, have nice day.


You are welcome @Reinhard-Korn

1- I’ve replaced the Column Rename node from your download with a Constant Value Column node within starting the loop, as it is more simple to configure for a variable.

2-The real solution is implemented in the 2 shaded nodes in the figure.

The approach you mentioned in your post by operating the months wasn’t right. Despite that you visualized the data in columns, the data habitat for a time series (months) is a column. So in fact you where trying to operate by rows as in an spreadsheet mindset.


1 Like

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