Hey,
So, I’m new to Knime and have been developing some easier floews and studying a way to build a flow to help me run a monthly analytical base, segregate it by start date and due date (contract ending date) and calculate the monthly amount of installments due to payment for this vendor.
The expenses invoiced in January must be paid in February, and so go on.
I have a base as below:
| Start Date (DD/MM/YYYY) | Number of Installments (Months) | Total Amount Due | Monthly Payment (Total Amount Due / No. of Installments) |
|---|---|---|---|
| 01/01/2024 | 3 | 3000 | 1000 |
| 01/01/2024 | 6 | 3000 | 500 |
| 01/02/2024 | 2 | 3000 | 1500 |
| 01/02/2024 | 3 | 3000 | 1000 |
And it must become like this:
(All dates in DD/MM/YYYY format)
| Start Date (DD/MM/YYYY) | Number of Installments (Months) | 01/02/2024 | 01/03/2024 | 01/04/2024 | 01/05/2024 | 01/06/2024 | 01/07/2024 | Total Amount Due |
|---|---|---|---|---|---|---|---|---|
| 01/01/2024 | 3 | 1000 | 1000 | 1000 | 3000 | |||
| 01/01/2024 | 6 | 500 | 500 | 500 | 500 | 500 | 500 | 3000 |
| 01/02/2024 | 2 | 1500 | 1500 | 3000 | ||||
| 01/02/2024 | 3 | 1000 | 1000 | 1000 | 3000 | |||
| MONTHLY PAYMENTS | 1500 | 4000 | 4000 | 1500 | 500 | 500 | 12000 |
The main goal is to be able to see, in an aggregate/grouped way, the monthly value to be paid each month, while also having an overlook of the entire period - from start date transactions dating a couple years back to the last installment (so far, 2030).
Also, there is new entries each month, so, as an example, we could have a 1.000 payment due in 12 installments, and another one of 6.000 due in 18 installments from next month - 01/03/2024 (DD/MM/YYYY).
What I am finding the hardest is to make Knime create like 60 columns for 60 monthly installments in 01/02/2024 (DD/MM/YYYY), and next month using not the actual column, but the next column and also rightfully calculating another 60 months.
I guess this is a rather complex problem, but would be grateful if you could at least point me in the direction of what to study in order to get this result - if it is possible.
Thank you all!
