IFNULL in Tableau

What is IFNULL in Tableau ?

IFNULL in Tableau is the Logical Function which is use to changes a Null value to some other values. It will replace nulls values to a another value and also replace one columns which have nulls values to another values from another field. IFNULL in tableau works for any data types nulls values like Null dates, text and numbers and so on. IFNULL function also accepts aggregate and non-aggregate values.

Note:

Its important that, the data type going in to IFNULL() function then same data type coming out must match. Let say if the field going in is aggregate value then field coming out must also be aggregated.

IFNULL function can also be use within another logical functions also like IF condition, CASE statements etc

IFNULL Syntax in Tableau

The syntax of Tableau IFNULL is:

IFNULL([Field_name],[Field_output_same_DataType])

Lets understand each expression for IFNULL() function:

• [Field_name] this could be any field from your dataset, like, date, region, country, profit
• [Field_output_same_DataType] this is the value given by you as static value or values can be bring from another fields from same dataset.

Lets take few examples to understand more

Example #1: Replace Null Values For Profit Column Using IFNULL()

Lets understand with below tables example for “Profit” Column. You can see that Profit column have NULL values in ID 3 and 5

And in Second Table the ID 3 and 5 is replace with zero with below IFNULL function.

IFNULL([Profit],0)

Example #2: IFNULL() To Replace Date column with Another Date Column

Let consider below table , Which have “Open Date” and “Start Date” in your data Source and ID 2 & 4 for “Open date” is missing or Null. You would like to replace this Null values in “Open Date” with “Start Date”

For this you need to create calculated field as date data-type, using NULL() function in Tableau.

Open New Date = IFNULL([Open Date], [Start Date])

The above calculated filed will replace the “Open Date” with “Start Date”. You can see the output in “Open New Date” column the Blue Highlighted ID number 2 & 4 have the same values as in “Start Date” (as this values are Nulls for “Open Date”).

Lets take few examples to understand more

Example #3: Replace “Total Revenue” Using IFNULL() in Tableau

lets take example if dataset have revenue field with NULL values and you would like to show zero instead of NULL/blank then you can use IFNULL() function in Tableau.

IFNULL([Total Revenue],0)

As shown below output that after using IFNULL function it replaces the nulls revenue with “0” numeric.

Example #4: IFNULL() To Replace Date value with Hyphen

Lets take example for Date data type value, if you will use IFNULL() function for date type then the output should also be data type or either you can change the date into string. So both way you can perform IFNULL on Date data type

Lets take example when data type is different and what error it will show

IFNULL([Order Date],"-")

The above function will return nothing it will show you the error in your calculated field window, as shown below image

Note:

Convert the date data type to a string by using function STR(). However, once the Date field is a String date type the the above error will get removed

Correct formula for IFNUL function for Date data-type is below:

IFNULL(STR([Order Date]),"-")

Example #5: IFNULL() for aggregated functions

you can use IFNULL() function on aggregated function also, however the both coming in and going out values should be aggregated, If any one of then is not the same then the above error will show again in calculated window of Tableau

Below is the example which will show the error

IFNULL(SUM([Total Amount]), [Total Sales])

Error:

Cannot mix aggregated and non-aggregated arguments with this function

Below is the example which is correction of above error:

IFNULL(SUM([Total Amount]), SUM([Total Sales]))

Conclusion

IFNULL() function can be used any data type to replace nulls values to zero or any other values, only insure that coming in and going out values should be same data-type values.