Hi I am new to Knime and hoping someone with more knowledge may be able to point me in the right direction please?
I have an excel spreadsheet as the main input. This holds financial data for the current Financial year and is updated monthly (month end results) by project. I would like to create a workflow that ‘knows’ what the current month is and produces a year to date figure for plan vs latest views vs actuals for each project that can then be totalled later on. The plan will have data in for every month during the year from month 1 (as may the LV but this is updated monthly looking forward). I need to be able to show what the accumulated values are for plan, LV and actuals from month 1 to current month and as the new excel file is uploaded each month, the workflow recognises the current month and sums up the values from month 1 to current month for plan, LV and actuals.
You can use that variable then to work out which months you are in.
How things progress then really depends on what the source data looks like, but will likely involve filtering the data set, aggregating it as per your logic, putting it back together and visualizing it.
As this is a finance use case you may also want to review what is on KNIME hub in Knime for Finance:
There’s also one example on Monthly and YTD Aggregations:
Please find attached sample data that would be available month end May. At month end June, the actuals would be added to Col R, for August to Col S etc. The LV’s may also change as the year progresses Sample Data for Plan vs Actual vs LV.xlsx (11.1 KB)
Thank you again for your help
Thanks for the data - makes it easier. Here’s a prototype:
Have commented “on the first lane” a bit - the other two lanes apply the same logic and at the end the Plan / Actual / LV YtD numbers are attached to the original input table.
With the column headers not including anything in date format and with your first month being April it was a bit tricky - so also take note of the comments in the variable expressions node where the current month is worked out as integer and this value is then being adjusted to make April the first month.
Thank you so much - I can see an overview but I can’t see any comments I’m afraid, cannot seem to zoom in on the workflow either but will work that out
Thank you again
You are welcome. You can download (or drag and drop into your KAP). “Comments” was maybe a bit of an exaggeration - I added some very brief explanatory text to each node of the first “lane” (e.g. “Flip Table”) for Tabler Transposer. Good luck with taking it further from there
Can’t you just install it? It’s free and honestly variable and column expressions nodes are amongst the most useful…
You can drag and drop from here into your KAP:
There probably are ways to make it work without, but unsure when I will find the time to think about that so first recommendation is to get that great extension!
