Please help in solving the Year to Date (YTD) amounts. I checked the available solutions but either their time grain is different (day level) or they are for single year, leading to simpler solution.
For finance department, they need to calculate the YTD amounts for multi year financial line items. The problem is described as below:
The data file ‘Line-aggregation-calculation.xlsx’ is attached.
There are two entities , E1 and E2
Each entity has 10 line items.
The financial year has 12 months, starting from 1 to 12
For each entity, for year 2022, month 12, the year-to-date value is provided in input-data tab of data file.
For each entity, for year 2023, 2024 & 2025, monthly transaction amount is provided in input-data tab of data file. It is not necessary that each line item would have monthly transaction hence in the input data, for many line items, if transactions are not there, the line for the month would be missing.
Goal:
For each entity, line item, year, month, calculate the year to date amount. The formula is as below:
OPBAL (year opening balance) = previous year, last month (month 12)'s year-to-date amount
year-to-date amount = OPBAL + sum of monthly transaction amount from first month 1 to current month
In the data file, year-to-date amount is computed in output-data tab for comparison. Also, calculations tab contains the illustration of year-to-date calculation formula.
Prepare a report of monthly and year-to-date amounts.
In the data file, output-report is manually computed for reference. The orange shaded part of the report is generated using Year to date amounts.
If I understand correctly you want to creating the rolling sum of amound for each Entity & line item combo over the total timeframe. So for each entity you need to calculate this:
You could loop over each entity & line item group, sort by year and month, then create a moving aggregate as attached.
You could use a mix of groupings on year or months to calculate specific aggregations, and if a month is 12 then add a ‘year-to-date flag’ but I think the moving aggregate is probably good enough to get you start.
Hi @wkhan , thanks for the workflow and sorry for the delay in reply. I am able to solve it in Excel but learning Knime to replicate the solution.
The solution has a good start point, but it has the below issues:
1. Handling of missing months where no transactions are not there
Lets take a sample input data set:
If you observe, there are missing months where no transactions are there, e.g. 4, 6, 8, 10, 11 months.
But when YTD is calculated, it would have values for the missing months. Since the transactions are not there, the YTD would be simply the previous YTD values as below:
In the workflow, the YTD values for the missing transactions months are missing.
I am thinking of enhancing the solution as below:
Extract years for which YTD values needs to be created, i.e. 2023, 2024 and 2025
Create a “Year-Months” table by crossjoining 12 months, 1 … 12 with above years to create 36 rows year and months combination.
Take left outer join of “Year-Months” table with input data joining on Year and Month columns and substituting Amount = 0 and Aggregation = “monthly”. It would fill up the missing months transactions with zeros.
On the above table, we can proceed with YTD calculation.
2. Incorrect Computation of YTD values:
If the starting month, i.e. month 12 of 2022 had both “year-to-date” and “monthly” aggregate data, then the “year-to-date” computation for subsequent months for 2023 year onwards would have incorrect values.
The correct implementation of YTD value is as below:
OPBAL (year opening balance) = previous year, last month (month 12)'s year-to-date amount
year-to-date amount = OPBAL + sum of monthly transaction amount from first month 1 to current month
Yes sorry this wasn’t meant to be the exact solution as I’m not as familiar with some of these things, but an example on how you could aggregate the data.
I think padding the data with a join or something to account for missing months would be fine.
You would likely need to split the data into two paths - one for ytd and one for monthly calculations to handle each one appropriately. If they are in the same tab in the excel you can use a row splitter now to separate the paths.
If you haven’t take one of the free courses it would jumpstart which nodes to use and is only a few hours of content.
Thanks for the reply. Your solution has given a very good starting point.
I see that Knime is making very good inroads in Financial (Office of CFO) community. I have a lots of good, useful and sometimes complex use cases for the CFO office. Most of it, I have solved it in Excel. But as Excel is very good for start, it is difficult to maintain and thats where I see the value in Knime. I have just started learning Knime full time and hope to get good at it soon.
