4 Replies Latest reply on Nov 6, 2015 9:10 AM by JT Holland

# Find the minimum date across four fields, if null use fifth date.

Good morning Tableau Community!

I have a report, in it I need to group rows by looking at four date columns, finding the lowest one while ignoring any null values. If all four fields are null, then I should use a fifth date column. The MIN function is limited to two expressions, I've tried to create a CASE function but to no avail. I wonder if someone far smarter than I can lend a hand?

So, if data looks like this:

Id    Col1    Col2    Col3    Col4    Col5
1    1/1/15   Null     Null     Null   1/2/15
2     Null      Null     Null     Null   2/2/15
3     Null      Null   5/4/15   Null   5/1/15
4    2/1/15   Null     Null     Null   7/2/15
5    8/8/15  8/16/15 Null    Null   3/2/14

The end result should be:
Id    Value
1     1/1/15
2     2/2/15
3     5/4/15
4     2/1/15
5     8/8/15

I'm fairly certain that once I get to this point I can figure out the sorting/grouping part, but it's getting the four (or five) fields down to a single value that's proven to be a tough one for me.

Thanks!

• ###### 1. Re: Find the minimum date across four fields, if null use fifth date.

Hello JT,

Have you tried Rank_Unique? To find the minimum value, it should be something like Rank_Unique(Max([Value]) and limit the filter range to 1 because the default rank setting is descending. Hope this will be helpful.

Best regards,

Junhe

• ###### 2. Re: Find the minimum date across four fields, if null use fifth date.

You would need to reshape your data which can be done in the data source or through pivoting in Tableau.

Then it's just a matter of doing a LoD calc.  One question I have is that your expected results for ID 3 and 5 don't match what the data shows.  Was that just a mistype?

1 of 1 people found this helpful
• ###### 3. Re: Find the minimum date across four fields, if null use fifth date.

IF (([Col1] < [Col2] OR ISNULL([Col2])) AND ([Col1] < [Col3] OR ISNULL([Col3])) AND ([Col1] < [Col4] OR ISNULL([Col4])))

THEN [Col1]

ELSEIF (([Col2] < [Col3] OR ISNULL([Col3])) AND ([Col2] < [Col4] OR ISNULL([Col4])))

THEN [Col2]

ELSEIF ([Col3] < [Col4] OR ISNULL([Col4]))

THEN [Col3]

ELSE IFNULL([Col4], [Col5])

END

1 of 1 people found this helpful
• ###### 4. Re: Find the minimum date across four fields, if null use fifth date.

Figured it out myself and I was making the issue so much harder than it needed to be.

MIN( MIN( MIN( MIN(

IFNULL([Date1], [Date5]) )

, IFNULL([Date2], [Date5) )

, IFNULL([Date3], [Date5]) )

, IFNULL([Date4], [Date5]) )

)