Comparing data with next year

Good Day Knimers :green_heart:

I have an issue, where I need to compare data with next year (2022).
The formula as in here
if( year(date(date#( “Time Period”,‘MMM YYYY’),‘YYYY/MM’))=’$(vNextYear)’ , “Consensus Selling Forecast (N-1)”,’’) as “NextYear N-1 Consensus Selling Forecast”,

in which vNextYear = Year(Today()) + 1;

I have done in the following way:

  1. Added Create Date&Time Range Node and wanted to cross join with my initial data. But, unfortunately, it did not go well, because my data 1 500 000 rows, and cross join had java heap space error and it took about one hour
  2. I used Date&Time difference node, where I took difference between data (Time Period Column) and current execution date&time.

image

As a solution, wanted to write if statement. But as you can see here, for 2021 and 2022 it gives 0, I thought I will give us -1 and my statement will be

But the result did not give me right answer, because Date&Time difference (in Years) gives 0 for 2021 and 2022

For me personally, the best option will be adding current sys date to the date, but dont know how to add it (except cross join)

Do you have an idea how solve this issue?

Many thanks, Karlygash :innocent:

1 Like

Hi @Karlygash , might be better to share your workflow and some data, and also some sample of the expected results so we can confirm we understood your formula.

EDIT: Sorry, I realize that the issue that you are having is with the date diff only, correct? It seems that you are ok with the rest of the formula, correct?

If that’s the case, I’ll only focus on the date diff issue then.

The issue here is that you are trying to get the difference in number of years from current date, that is why you are getting these results. If today’s date is 2021-09-01, then the following differences in years make sense:
From 2018-12-01 to 2021-09-01 there are 2 full years
From 2020-01-01 to 2021-09-01 there is 1 full year
From 2021-12-01 to 2021-09-01 there is no full year, so difference is 0
From 2021-09-01 to 2022-04-01 there is no full year, so difference is 0

Do you just want to compare the year? You can extract the Year information of the dates and compare with the Year information of today’s date, and then do a Math arithmetic to compare

2 Likes

Yes, I wanted to do extraction, but dont know how to add current year as new column
I wanted to add Create Date&TIme node and after wanted cross join them, but it did not work.
Because my data is about 1 500 000 rows for now, it executed about one hour and had java heap space error

The problem is that I do not have current year in my dataset

Hi @Karlygash , ok, I hear you. So, you want to avoid using multiple steps that will create multiple nodes that will copy over the 1.5 million rows in each of the nodes, I get it.

In that case, since you will be using Column Expressions anyways, you can do the manipulation the a Column Expressions, like this:

currentYear = new Date().getFullYear();
toInt(currentYear - substr(column("Time Period"), 0, 4));

Results:
image

For integrating with your current Column Expressions, you may not even need to create a column for the Diff, which will avoid repeating the 1.5million rows. You can just do:

currentYear = new Date().getFullYear();
diff_based_on_year = toInt(currentYear - substr(column("Time Period"), 0, 4));
if(diff_based_on_year == -1) {
  column("Consensus Selling Forcase (N-1)")
}

EDIT: Forgot to attach the workflow. Here it is:
Get diff based on year via Column Expressions.knwf (6.7 KB)

3 Likes

getFullYear does it get Current Year, right?
so, in order to get + 2 year (2023), I can write the following expression, right?
currentYear = currentYear+2
I have never known such opportunities of Column Expression node)

Yes, that is correct, but you would want to make sure it returns you an Int instead of a Double, so you can use toInt() function.

You can also try it for yourself, just create a new Expression:

And you can play around:

You can highlight the line you want to Evaluate, and then click the Evaluate button to see what the result is.

EDIT: And of course, you can delete that test Expressions after. Highlight the Expression you want to remove, and remove it:

2 Likes

Thank you very much! :blush:
Your solution is really helped ! :innocent:

1 Like

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