6 Replies Latest reply on Apr 17, 2017 2:31 PM by Justin Larson

# Max of two Date Fields - When one is null the Formula gives me a null value instead of the MAX

So I have two date fields.  I'm trying to pull only the max.  When I use the MAX formula however on instances where one field is blank, instead of giving me that MAX date, it gives me a Null value.

Can anyone help me with this?  My file is unfortunately 80 MB so I can't attach it.  Thank you!

• ###### 1. Re: Max of two Date Fields - When one is null the Formula gives me a null value instead of the MAX

It is really hard to say with out seeing your data structure and calculations,

can you take an extract of part of the data and publish a packaged workbook with that?

• ###### 2. Re: Max of two Date Fields - When one is null the Formula gives me a null value instead of the MAX

Jay,

You may want to incorporate an ISNULL clause:

IF (

IF ISNULL([Date A]) then date('1900/01/01') else [Date A] END)

>

(IF ISNULL([Date B]) then date('1900/01/01') else [Date B] END)

then [Date A] else

(IF ISNULL([Date B]) then date('1900/01/01') else [Date B] END)

end

1 of 1 people found this helpful
• ###### 3. Re: Max of two Date Fields - When one is null the Formula gives me a null value instead of the MAX

Hi Charles,

I'm assuming you're using a formula like MAX([Date1], [Date2]).

I was able to solve the issue by slightly tweaking that and using:

MAX(IFNULL([Date1],[Date2]),IFNULL([Date2],[Date1]))

Essentially what this does is assigns [Date2] as the value for [Date1] when [Date1] is null (and vice versa). This should work and get you your desired result.

Hope this helps!

Michael

1 of 1 people found this helpful
• ###### 4. Re: Max of two Date Fields - When one is null the Formula gives me a null value instead of the MAX

This is by design. Most common approach is to supplant nulls with a value, often a zero. This is how you would usually come across it:

max( zn([field1]) , zn([field2]) )

zn stands for Zero Null, and just replaces any null with a zero value for artithmetic functions, like Max.

2 of 2 people found this helpful
• ###### 5. Re: Max of two Date Fields - When one is null the Formula gives me a null value instead of the MAX

Hi Justin,

Just remember we have to compare apples to apples! Unfortunately we can't compare 01/01/2017 to 0, so we have to either feed a date, or default to another date field. In SQL this would be a simple coalesce, the tableau equivalent is If ISNULL([Field 1]) then [Field 2] Else [Field 1] etc

Galen

2 of 2 people found this helpful
• ###### 6. Re: Max of two Date Fields - When one is null the Formula gives me a null value instead of the MAX

Right you are, Galen!

I'm brushing up on Tableau, having not used it much lately, and forgot it's a little more persnickety than T-SQL on this front.