Formula IF

[Francisco Aspillaga]

Hi all,

If I have two columns with dates: Date1 and Date2, what epilepsia this Excel fórmula be like in Spotfire?.

 

=If (Date1=“”,””,If (today()>=Date1,If (Date2>1,Date2-Date1,today()-Date2),””))

 

thanks!

[David Boot-Olazabal]

Hi Francisco,

Not sure if your Excel formula is complete, it looks like you probably meant =If (Date1=“”,””,If (today()>=Date1,If (Date2>Date1,Date2-Date1,today()-Date2),””)). Is it correct that it you start with a 'normal' If and then you use nested If?

You may be able to copy and slightly adjust the same in Spotfire but I would advise to use a CASE statement in Spotfire. You have a better overview of the different rules (and the order of the rules) you want to run the statement against.

It probably looks like this:
CASE

WHEN [Date1]="" THEN ""

WHEN today()>=[Date1] THEN today()-[Date2]

WHEN [Date2]>[Date1] THEN [Date2]-[Date1]

ELSE ""

END

 

Kind regards,

David

[Francisco Aspillaga]

Thank you very much for your help David.

I follow the formula but it gives me this error: Invalid type for function call '='.

 

regards

[David Boot-Olazabal]

Hi Francisco,

Right, I can reproduce the error message with the initial CASE statement.

What we need to take into account, is the way we combine different types of data format (so real and string). It's always kind of tricky with date calculations.

I have created a working CASE statement, although you have to replace the StartDate and EndDate variables with your own Date variables of course:

CASE
WHEN [EndDate] IS NULL THEN NULL
WHEN Today()>[StartDate] THEN DateDiff('day',Today(),[EndDate])
WHEN [EndDate] > [StartDate] THEN DateDiff('day',[EndDate], [StartDate])
ELSE 0 END

As you can see, the outcomes of each and every rule is either NULL or a number and that is something the Spotfire engine is accepting.

Kind regards,

David