Jump to content
We've recently updated our Privacy Statement, available here. ×

Formula IF


Go to solution Solved by David Boot-Olazabal,

Recommended Posts

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

Link to comment
Share on other sites

  • Solution

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...