Datediff with null in Spotfire

Question

I'm trying to calculate the difference between two dates, but if the "end date" has a null, like in rows two and four, then I'd like to put in "today"....the data table looks like such.

Hire Date             Term Date = "end date"          
01/01/2019            05/01/2020
05/04/2018            
09/17/2019            03/18/2020
10/19/2018

I was think something like this would work but it's not...

If([Term Date]<>NULL THEN DateDiff("year",[Hire Date],[Term Date])
ELSE  DateDiff("year",[Hire Date],Today())
END 

Thanks in advance!

Answer 1

You can use a SN() which is substitute Null.

DateDiff('year',[Hire Date] ,  SN([TermDate],Today() )   )

or use a case statement instead of a IF as your format for an IF statement is incorrect - If(argument, True, False) is the proper format but a case is easier to work with at some points .

Case when [Term Date] is null then DateDiff("year",[Hire Date],Today()) 
else DateDiff("year",[Hire Date],[Term Date]) 
end