8 Replies Latest reply on Jan 25, 2019 2:58 AM by Ankit Bansal

# How to get minimum among the columns (May contain Null values)?

Can anyone please help me to find the minimum of the columns when some of the columns may contain null values?

If I use Min() it will out the column which contain null value as minimum. But I need minimum of the non-null columns.

• ###### 1. Re: How to get minimum among the columns (May contain Null values)?

Simply use ZN() function.

Watch first 1 min of this video:

ZN() function will convert the Nulls to 0 and then you will have to basically pick second largest value if your column doesn't have negative numbers.

• ###### 2. Re: How to get minimum among the columns (May contain Null values)?

But how do you pick second largest value among the columns?

• ###### 4. Re: How to get minimum among the columns (May contain Null values)?

A sample workbook can help you faster though with a fake data may be...because Rest all is Guessing which won't work for u

• ###### 5. Re: How to get minimum among the columns (May contain Null values)?

Hi Deepak,

I have attached a sample workbook. Please try with it.

• ###### 6. Re: How to get minimum among the columns (May contain Null values)?

Sudip,

Use the below calculation:

• ###### 7. Re: How to get minimum among the columns (May contain Null values)?

The nested ifnull statements can become cumbersome if you have more than two or three fields to compare. I had a case with 5 fields. Nesting ifnull statements to account for every permutation of nulls with 5 fields ends up being nearly impossible to pull off without errors (if A is null, if B & D are null, if B & C & E are null...). I came up with a solution which is a little Y2Ky, but works.

In my case, I had five time stamps for each record, some of which could be null (they were never *all* null). I needed to know the earliest of the times, ignoring the nulls. So, I created a calculated parameter for each, where, if the value was null, it was to assign today's date + 1000 years:

In a non-date field, you could just make it a large enough number to never interfere with the other values - several orders of magnitude bigger than the largest expected value. This way, the calculated version is large enough that it could *never* be the min. Then, just create a simple nested min check for the different fields:

min(min(min(min([Calc Null FIELD1],[Calc Null FIELD2]),[Calc Null FIELD3]),[Calc Null FIELD4]),[Calc Null FIELD5])

To be robust, if the all-null case could happen, you'd have to add a check for it.

• ###### 8. Re: How to get minimum among the columns (May contain Null values)?

You are right. That's making sense. I wish Tableau will add greatest and least functions for single row comparison. somethink like:

Least(col1,col2,col3,col4....)

That will make calculation simple.