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.
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
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.