Calculating Year To Date Figures

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.

Any help would be greatly appreciated - thank you

Might be good for you to share some example data (anonymised).

Very generically:
You can grab the current date using Date&Time Configuration Node like this:

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:

5 Likes

Thank you so much for your super quick response!

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.

2 Likes

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 :slight_smile:

1 Like

I cannot thank you enough!

I don’t have the Expressions extension! Is there a way to do this without it do you know please?

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!

1 Like

Thank you - tried to install but failing due to our firewall - will try to get help from IT - thank you again