Calculating Monthly Change in Financial Accounts

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)

image

  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!

4 Likes

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.

1 Like