6 Replies Latest reply on Apr 12, 2019 7:57 AM by Alex Kerin

calculation if date=null

Hello,

my problem is that i have a date where i have to do a calculation like the following:

IF [date1] != NULL THEN

NOW()-[date2]

ELSE

[date1]-[date2]

END

the date1 is filled with null or empty when the database entry is not finished, and i want to make a if question with this date1, but the tableau formula shows me the following error:

can't compare datetime and NULL values.

can someone tell me how i can compare dates?

thanks

Armin

• 1. Re: calculation if date=null

You just need to use isnull([Date1])

See attached

1 of 1 people found this helpful
• 2. Re: calculation if date=null

Hello Alex,

I have a similar problem on date selections. Here is the scenarios.

Problem1. I have start date and end date. I Should not allow user to select start date lesser than end date and similarly end date should not be greater than start date. What is the best possible solution for this.

Problem 2: There is start date and end date, say from December 2014 to March 2015. There are few dates between these months where there is no data, say Jan 1 to Jan 4 - There is no data. If a user select this date, I need to display an error message - NO DATA FOUND.

• 3. Re: calculation if date=null

Hey,

Can you expand on the Problem 1,

Am i right in saying that you have Start Date as a filter option on your dash board as well as end date, but do not want them to cross?

I think you would need to create a DATEDIFF parameter where the DIFF must always be >1.

ill think on how to build that..

[EDIT

I wrote this:

(DATEDIFF('day',[STARTDATE],[ENDDATE]))>1

Then set the filter to "True" and then the 2 date filters to "ONLY SHOW RELEVANT VALUES"

]

Problem 2 - Is a simple Calc if you want the reader to find "NO DATA FOUND"

Create a Calculated Field and call it DATA

Then write something like

IF ISNULL([YOURDATA]) THEN "NO DATA FOUND"

ELSE [YOURDATE]

END

Then use this field over the other data one, this should allow all date ranges to be returned then.

Ben

• 4. Re: calculation if date=null

How to use If isnull with 2 different cases

And or

I have 2 Columns where Date is Null

Start Date  &  Actual Start Date

Want to do the calculation like

if isnull Start date and Actual start date then "0" else "1"

but it gives me error Kindly guide how to use with 2 different cases.

• 5. Re: calculation if date=null

Hi Alex,

I have a similar case but when I am applying  ISNULL, it is selecting all the values which have null values.

I am using it for a date column. My requirement is to exclude the null values.

What can I do?

• 6. Re: calculation if date=null

NOT ISNULL()

Try that.