Calculate single month values from Pivot with cumulative values

[Reinhard-Korn]

Hello,
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
etc.

[gonhaddock]

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

BR

Data Wrangling _ Deconstructing Aggregated Values – gonhaddock

[gonhaddock]

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.

Regards

[Reinhard-Korn]

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.

[gonhaddock]

You are welcome @Reinhard-Korn

Comments:
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.

BR