Hello @Master29
I am taking a look into the problem and it has some more complexity than expected from the start. As you have to make assumptions aiming to design an unique standard report, matching in terms of dates…
I hope that you have new lessons learnt . I am learning new things with this challenge by dedicating some of my time as well, and becoming quite enthusiastic with KNIME potential for Financial tasks, combined with some coding skills (from an Excel hater). Anyhow …
The first assumption was to define a matching week day. I stablished Mondays:
- ‘Full Cycle starting Period 01 Date’ happens next Monday to your [Start Date] column. forcing all the workflow to match Mondays for every single Period
- ‘Looking Forward Period 01’ for starting LF report happens next Monday to your System Time when executing the Workflow (You can adapt it by customizing a forced entry in your Input Data)
Then the workflow starts by processing the initial Input Data
For the initial Input data I left the ‘Life left (weeks)’ column with empties, as they need to be processed to match with the automated ‘LF Period 01’ start. In this example, it will happen on next Monday (November the 29th, 2021). Part of the Loop in previous workflow was convenient to place within the metanode in order to calculate the necessary parameters.
The rest of the workflow is piece of cake:
Comments:
1.- Looking at processed input data. Our old friend ABC asset has passed already the contractual terms (Life left (weeks) == 0), becoming an ‘Out of Full Cycle’ asset. This is filtered out in the ‘Rule-based Row Filter’ node blocking to pass into the Loop.
2.- In the number 2 is right where the Standard report is defined. This format helps to fill-in with empties the reporting tails, by joining within the loop.
The workflow ends with two outputs:
-
The aggregated one proposed in the challenge.
-
The detailed numeric one to trace the results
Conceptually this is over from this side.
BR
P.S. Please take a careful read on the Disclaimer section in Description.