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:
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
I used Date&Time difference node, where I took difference between data (Time Period Column) and current execution date&time.
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
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
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));
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:
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)