Calcualtion of YTD

I have forecasted number for the full year, and want to calculate YTD numbers till the current month.

The catch is that as per rule we have to use quarter numbers and only during the running quarter period we have to use the month column for calculation after the summation of earlier quarters.

eg if the current month is Oct, the YTD will be Qtr1 + Qtr2 + Qtr3 + Oct
Here is the sample of my column header structure:
“Forecasted 2020 JAN MTH” (for the month)
“Forecasted 2020 MAR QTD” (for quarters)

Right now I am using a column expression node to add the columns manually since the number of columns is not fixed every month.

Is there anyway make this automatic using Knime only node?

Thanks in advance :slight_smile:

Hi @Loke2707 -

Can you post an example workflow, including a bit of the data, that demonstrates what you’re already doing with the Column Expressions node? That might help the process along, to see if there’s another way to accomplish this using other nodes.

Hi @ScottF I know I am kind of late but this is the solution I had found.

YTD Sample.knwf (67.3 KB)

In this work flow I am trying to Identify current month of Actuals number and Plan numbers and there Variances (change due to fx rates). Identification of these variances are the tricky part for which I had asked the question.

In the example I have to identify how many quarters have passed and how many months gone by post last quarter. and then only for FX I had to do a weighted average calculation eg. (Qtr1 *number of days in quarter + Apr * number of days in Apr)/ (number of days in April + number of days in Qtr1)

Although my solution seems working, I am just wondering If there could be a better solution ? since my actual data is very big and each and every nod is contributing to a good amount of processing time

Hello @Loke2707,

I haven’t gone into details but have seen you are using number of Column Expressions nodes. Although this node got some decent speed-up in one of previous versions and offers if/else logic with string/numeric operations it’s not a node you want to go with if looking for better processing time. So I suggest to replace it with Rule Engine (and where necessary in combination with Math Formula/String Manipulation) where possible.


1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.