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.
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
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)