Home » Tableau » IFNULL in Tableau

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 can convert nulls values to a another value and also convert nulls to another values from another field. IFNULL in tableau can convert 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_sameDataType])

Lets understand each expression for IFNULL() function:

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

Lets take few examples to understand more

Example #1: Simple IFNULL() in Tableau

lets take example if dataset have revenue field with NULL values and you would like to show zero insted 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.

output of IFNULL in tableau

Example #2: IFNULL() use for Date value

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

showing error in calculated field window for IFNULL in tableau

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 #3: 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.