There are three ways to hide Null values in Tableau:
The first is to Format the nulls, second is to Filter out the null values, and third handling nulls with a Formula.
To begin, what is a null value? A Null in Tableau means there is no data in that field, it is unknown, a blank value.
Whether it’s better to handle null values with a Format, a Filter, or a Formula depends on the circumstances.
Format – Hide the Null values by Format Special Values
One of the simpler ways to remove null values in Tableau is to format them. This option isn’t always available, it only works for continuous (green pill) measures. This technique is recommended to remove nulls from a line chart.
Right-click on the Measure pill and select Format. In the format window, go to Pane and hopefully the Special Values will be there.
Select Hide in the Special Values section of the pane to hide the Null values.
Filter – Remove Null values using a Filter
Filtering is another way to remove null values in Tableau.
The technique and suitability of this option differs slightly depending on whether the Null value is found in:
- a discrete (blue) dimension – i.e. a Product field
- a continuous (green) measure – i.e. SUM([Sales])
How to filter continuous (green) Null values
Put the continuous pill, for example SUM([Sales]), to the filter shelf. Filter out the null values using the Special section.
In the Special section of the filter you will see Non-null. Select ‘Non-null values’ and it will hide the null values. However this could inadvertently filter out some of the data you want including so be careful when using this.
How to filter discrete (blue) values
To filter Null dimensions / discrete measures put the pill to the filter shelf and deselect Null. The Null value will appear in the list with discrete values where it is simple to remove.
If applying this filter to a dimension be careful. Filtering out a dimension value will remove all records from the worksheet. For example, in a case where you’re also showing the totals, the totals would reduce by the amounts filtered out.
With a dimension, it may be better to use a formula to replace the Null values.
Formula – Handle Null values in Tableau using a Formula
There are three Tableau formulas specifically designed to handle Null values:
Let’s run through these in reverse order.
The Tableau ZN function – Replace Null with zero
The Tableau ZN function converts Null numeric values to 0. Once your Null is zero, you’re able to format zero to dash, etc. Or just leave as 0, of course.
ZN only works with numbers – i.e. Integer, Float. Entering a String or Date field to ZN will cause an error. The syntax of ZN in Tableau is:
Converting Null to zero with ZN is very useful when aggregating, such as a SUM.
For example 3 + Null = Null.
Often it would be preferable for that sum to equal 3. Therefore the formula could change to 3 + ZN(Null), which calculates the same as 3 + 0.
When dealing with numbers ZN is great, it’s far simpler than delving into IFNULL statements or combining IF statements with ISNULL.
IFNULL in Tableau to Handle Nulls
The IFNULL function is a simple way to convert a Null value to something else. It works for numbers, string and dates.
The logic behind IFNULL is very simple. It checks if the field is null and, if it is null, converts it to what you specify.
However, the data type going into IFNULL has to match the data type coming out.
For example, if checking whether a number is null, the replacement has to be a number. Or if checking if a date is null the replacement value also has to be a date. Otherwise, it errors.
Tableau IFNULL with an integer
Following on from the ZN example, this also works to convert Null integers to 0. However it’s a longer way of writing ZN() so I don’t recommend you do this.
[IFNULL to 0]: IFNULL([NumericField],0)
Tableau IFNULL with a date
Remember the input and output have to be the same. Therefore the replacement value also has to be a date.
[IFNULL to 1 Jan]: IFNULL([DateField],#2020-01-01#)
This converts Null dates to the 1 Jan 2020.
Tableau IFNULL with a string
Replace a Null text value with any text you want.
[IFNULL to text]: IFNULL([TextField],"whatever text you want")
Tableau IFNULL to replace null values with values from another field
IFNULL can also substitute the null values in one field with the values from another field. For example, using date fields, replace the Null values in DateField with values from OtherDateField:
[IFNULL Replace Date]: IFNULL([DateField],[OtherDateField])
Another example, this time with strings, replace Null Country field values with the City value:
ISNULL – or Is Not Null – in Tableau
ISNULL differs to the other formulas. It only checks whether a value is Null. ISNULL doesn’t convert a Null to something else.
It is a Boolean function, it returns either True (it is null) or False (it is not null).
Usually this is used within an IF statement. In plain English, if a value is null then do something, else do something else:
IF ISNULL([Field]) THEN "Do something" ELSE "Do Something Else" END
It can be a long-winded way to write IFNULL or ZN:
IF ISNULL([NumericField]) THEN 0 ELSE [NumericField] END
A benefit of using ISNULL in an IF statement is additional flexibility. The example below would not work using IFNULL:
IF ISNULL([DateField]) THEN "No Date" ELSE LEFT(DATENAME('month',[DateField]),3) + "-" + STR(YEAR([DateField])) END
The above formula does the following:
When the date is null it shows the text “No Date”
When the data is not null it converts it to MMM-YYYY. Note it converts this date to string. The data types returned from this IF statement must be the same. As “No Date” is a string, the date also has to be a string.
Is Not Null in Tableau
Many software/programming languages use IS NULL and IS NOT NULL instead of the boolean ISNULL of Tableau.
For example, this is common for IS NULL (but doesn’t work in Tableau):
IF [FIELD] IS NULL THEN A ELSE B END
In Tableau this would be:
IF ISNULL([Field]) THEN A ELSE B END
To write IS NOT NULL in a Tableau calculated field means altering the order of those words to:
In other languages IS NOT NULL, is often written:
IF [FIELD] IS NOT NULL THEN A ELSE B END
In Tableau this “is not null” formula is:
IF NOT ISNULL([Field]) THEN A ELSE B END
How to count Nulls in Tableau
To count Nulls in Tableau, use ISNULL to convert each Null to True and each not null to False.
ISNULL returns either True or False. Wrapping a boolean in INT converts True to 1 and False to 0. Therefore, the formula to count nulls in Tableau is:
Sum that field to count the null values.
Replace Null with 0
NOT ISNULL() also works as an (unrecommended) alternative to ZN, to replace a Null value with a zero:
This is useful where the business rules are more complicated than simply replacing all Null values with 0. For example, perhaps only those Nulls with a category of chairs should convert to 0:
INT(NOT ISNULL(IF [Category]="Chair" THEN [Field] ELSE something else END))
There you have it, the main ways of handling null values in Tableau.
If the above isn’t enough for you, there are a number of other examples in this post on the Tableau site.
There you have it, the main ways of hiding null values in Tableau!