Hi All,
I am trying to do a simple depreciation forecast workflow in knime.
I can get the total forecast to work i.e. number of weeks per month multiplied by rate. However, the problem i face is when an asset “completes” its life at any point within in the year and thus it can be 1st of a given month or the 3rd week in a given month.
Can anyone suggest how i can compensate for this? I tried using loops etc but i just cant get it to work.
Further info - i know the start date of depreciation and the assets useful life end date.
Look forward to everyone’s assistance with this.
Thanks
Hi @Master29 ,
First of all, a warm welcome to the KNIME forum!
If I understood you correctly you want to have the granularity to the number of weeks. In that case you can use Date&Time Difference – KNIME Hub node. There are example workflows if you scroll down to the bottom of the page. You can use this example workflows to learn how to get the differences between two dates, e.g., in weeks.
Best,
Temesgen
2 Likes
@temesgen-dadi thank you! Yes 1st post of many (i guess)!
You got part of it… I have managed to get it to weeks already using the date and time difference node etc.
What i am struggling with is to have the “calculation stop” when the useful life of the assets ends. This end position differs for every asset as each have different start dates and also difference life lengths.
For example:
I need a calculation or node that can auto work out that for P3 only 3 weeks out of the 5 weeks needs to calculated in terms of cost.
1 Like
Sorry I am a complete stranger to the topic.
Where do the values (P1, 4), (P2, 4) & (P3, 5) come from?
If they are always fixed, you could multiply the rate by the min(LifeLeft - (4 + 4), 5)
It would be great if you share the workflow (exported) with a sample data. This is in general a good practice
Best,
Temesgen
@temesgen-dadi not to worry.
So P1, P2 and P3 stands for “Periods” So Period 1 etc. This is a finance/accounting term. In laymans terms P1 could denote any particular month in a companies Financial Year for example March-2021
the 4,4,5 is the number of weeks within that period. A companies financial year usually is broken down in to “x” amount of weeks within a period. Some periods will be 4 weeks some 5 weeks depending where they determine the monthly cut of is.
So therefore the number of weeks is predetermined for each period within the financial year.
The above snippet is exactly the information i have managed to get to so far in my knime workflow. I am struggling to find a node or way to calculate the cost for each month.
When a period is “fully utilized” because there is enough weeks its easy to calculate using a math node etc. But the problem i face is when say i get to period 3 (P3) there is only 3 weeks left that needs to added into P3 and not the usual 5 week cost.
I am looking for a way to auto calculate this issue.
Hello @Master29 and welcome to the KNIME community.
Take a look to this workflow, and let me know if it answers your challenge.
20211118_depreciation.knwf (37.1 KB)
BR
1 Like
@Master29
This version that I am posting now, doesn’t match your Example Table in terms of number of weeks. But this is more accurate in terms of dating; if I understand that ,‘Start Date’ is the full cycle start of the project, and you are looking forward beyond week 254 to the end life in week 265 (these are the latest 11 weeks of the project).
This results in a P01 with 2 weeks, P02 with 4 weeks and a P03 with 5 weeks… Anyhow, the mechanics are in the workflow.
20211118_depreciation_v02.knwf (43.7 KB)
Regards
PS .- You can also calculate the 11 remaining periods with starting date in week 254 (2021-09-03) going back to 4 - 4 - 3 (?)
1 Like
@gonhaddock thank you!
Will defo take a look at this. Looks promising!
1 Like
@Master29
I found this subject to be a very interesting challenge, these time handling mechanics can be applied for a few different type of analysis.
I thought that it was worthy to move the workflow into my KNIME Hub. Then, for keeping the vesioning updated; and any comment or bug related to the workflow, it can be captured in the hub space.
BR
2 Likes
@gonhaddock the last flow you unloaded seems to have down the trick as what to what is needed that is. (after linking it to my live data.
However, not quite sure where/how to now include multiple assets with varying end of life dates?
What node do i need to look at to tweak?
Thanks
1 Like
Hi @Master29
It should be quite straight forward as it is now with small enhancements:
- Arrange your projects by rows in the same format as it is now
- Replace the ‘Table Row to Variable’ node with a ‘Table Row to Variable Loop Start’ node
- Embed a ‘Loop End’ node in between ‘Pivoting’ and the ending ‘Joiner’
Maybe the Joining method could need some adjustments but I think that you can fix it 
Otherwise, you can always come back with the findings.
BR
P.S.- As I remember, the workflow in KNIME Hub has some improvements in term of flowing variables compared to the v03 posted in hurry. You may have to check if you are using the v03 as a template.
P.S.2.-
Aiming to concatenate the data in the ‘Loop End’ you may have to arrange empty periods fill-in up to… or an anomalous high number of empty periods -option1-, or to the exact number of periods of the largest project in terms of time -option2- (this last option requires some more of ideation energy); this has to be somehow just before the Loop End.
In the case for option1, remove empty columns once the Loop ends will be a must.
This happens because the Loop Ends nodes in general, are expecting to bind objects with similar number of elements (rows or columns)
@gonhaddock so tried following your advice re adding the "Table Row to Variable loop start " node and also adding in a “Loop end” node.
All works ok until i get to the Loop end. It says this:
I have tried most of today to try and correct it looking at your P.S.'s above but i am just stuck. I bet it is something really easy and obvious that i am not seeing.
This is the extract of the input data (that replaces the table creater node in your flow):
Hopefully the above might help with the last piece of my puzzle.
Thanks
1 Like
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:
Conceptually this is over from this side.
BR
P.S. Please take a careful read on the Disclaimer section in Description.
@gonhaddock thank you for this. You dont perhaps have the workflow for this solution i can download?
Also, i am new to metanodes so that something i plan on getting to know more about etc.
From the “aggregated view” snippet you provided it looks like the workflow you did works. Again thank you for your help on this.
Hi @Master29
It was published 4 days ago in KNIME Hub 
You will be able to download from posted link
Regards
It was “hidden” at the top. I searched that page i swear! Anyway found now! Again thank you so much
2 Likes
@gonhaddock just to give you a heads up. After a couple tweaks i have managed to get the output i need, except for 1 thing… which i have been struggling to work out.
I am struggling to get the loop to recognize a start date that isnt already the start date of the forecast i.e. 28/11/2021. So for example the asset starts depreciation further in the future say 06/02/2022 instead of when the forecast start date is the 28/11/2021.
Reason for a future start date is because i am trying to predict what the forecast will be inclusive of not yet bought assets (but will be soon).
So instead of the loop calculating and putting the cost in P1 i need it to put it (start putting it) in P3.
Hoping you can point me in the right direction with regards to which node to tweak etc. or if I should put in another step/flow altogether.
Thanks
Hello @Master29
I see, you are trying to display Business Opportunities together with Accounting Forecast.
I am busy these days for testing but, I think the concept is already implemented, so you just have to ideate the right approach.
Conceptually the implementation can be as simple as duplicating the columns LookingForward and LifeLeft just to operate within the loop (two Rule Engine just before the Loop Start can handle the task). These duplicated have to be conditional to a new attribute column in your data like:
IF $PROJECT_TYPE$ LIKE “Accounting” => Looking Forward | Life left (weeks)
IF $PROJECT_TYPE$ LIKE “Opportunity” => Full Cycle | Life (weeks)
Then, I guess it will fill in with empties the periods ‘before’, and it will be incorporated normally into the results.
BR
