Calculating Monthly Change in Financial Accounts

[gutembergueps]

I’m a bit new to KNIME, so this might be a simple question for the most of you. I am trying to create a single row of data which calculates for me the change in Accounts Receivable (and other working capital line items) on a monthly basis. I am not missing any months, and my data looks like this:

Accounts Receivable Closing Balances per Month

ACCOUNT_NAME	PERIOD_END_DATE	CLOSING
Accounts receivable	1/31/2018	100
Accounts receivable	2/28/2018	200
Accounts receivable	3/31/2018	300
Accounts receivable	4/30/2018	355
Accounts receivable	5/31/2018	410
Accounts receivable	6/30/2018	405
Accounts receivable	7/31/2018	460

I essentially would like my output to look like this:

ACCOUNT_NAME	PERIOD_END_DATE	CLOSING
Accounts receivable	1/31/2018	100
Accounts receivable	2/28/2018	100
Accounts receivable	3/31/2018	100
Accounts receivable	4/30/2018	55
Accounts receivable	5/31/2018	55
Accounts receivable	6/30/2018	-5
Accounts receivable	7/31/2018	55

As you can see, my requirements are as follows:

• Find the previous month and calculate the closing balance difference from the current month (i.e. Previous minus Current Month)

[RedJungleBear]

1. Lag the CLOSING column by 1 and skip the last incomplete row
2. Set missing values in the lagged CLOSING column to “0”
3. Subtract the lagged CLOSING column from the initial CLOSING column (and set the output to replace the initial column)
4. Filter-out the now unnecessary lagged CLOSING column

ETA: And welcome to the forum!

[Adrix]

Hi,
I am afraid in the real life example you won´t be able to replicate the lag column
1 - Yours data shows duplicated rows
2- Probably you will have another acounts . Which by it self will invalidate any sorted calculation.

I would recommend a join picking up year and months as keys . Some work to calculate the previous key but in the end more robust IMO.

Calculating Monthly Change in Financial Accounts-adr – adrix