There are three ways to hide Null values in Tableau:
The first is to Format the nulls, second is to Filter them out and third is by Formula.
The most appropriate option to remove the null values 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 out. Unfortunately, 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.
Filter – Remove Null values using a Filter
Filtering is another way to hide null values in Tableau. This differs slightly if the Null value is in a discreate (blue) dimension – i.e. a Product field – or a continuous (green) measure – i.e. SUM([Sales]).
If applying this filter to a dimension please 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, a better option is often to use a Formula to replace the Null values.
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.
Handle Null values using a Formula
There are three formulas designed for handling Null values:
Let’s run through these in reverse order.
Using the Tableau ZN function to convert null values
This is the simplest function to explain. It simply converts null values to 0.
ZN only works with numeric values. Entering a String or Date field to ZN will cause an error.
Converting Null by formula 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).
When dealing with numbers this is great, it’s far simpler than delving into IF statements with ISNULL or IFNULL statements.
Using the Tableau IFNULL function
This function is a simple way to convert a Null value to something else. It works for numbers, string and dates.
The function is very simple. It checks if the field is null and, if so, converts it to what you specify. This could be another field or it could be a value.
However, the data type going in 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.
Using 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)
Using 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.
Using IFNULL with a string
This converts null (blank) text to the words “whatever text you want”.
[IFNULL to text]: IFNULL([TextField],”whatever text you want”)
Use IFNULL to substitute one field with another
IFNULL can also substitute the null field for another. The fields both have to be the same data type.
For example, using date fields:
[IFNULL Replace Date]: IFNULL([DateField],[OtherDateField])
Another example, this time with strings, replace Null Country field values with the City value:
Using the Tableau ISNULL function
ISNULL differs to the other formulas. It doesn’t convert a Null to something else, it only checks if a value is null.
It is Boolean, 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 A, else do B.
Is Null and Is Not Null in Tableau
Many softwares / programming languages use IS NULL and IS NOT NULL instead of the boolean ISNULL of Tableau. For example:
IF [FIELD] IS NULL THEN A ELSE B END
In Tableau this would be:
IF ISNULL([Field]) THEN A ELSE B END
Similarly with IS NOT NULL, this is often written:
IF [FIELD] IS NOT NULL THEN A ELSE B END
In Tableau this “is not null” formula should be:
IF NOT ISNULL([Field]) THEN A ELSE B END
ISNULL is also a longer way to refer to other Tableau Null functions
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 the additional flexibility you gain. 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.
ISNULL is useful to create a simple formula to filter out null values. For example to remove the Null [Country], create a calculated field with the formula ISNULL([Country]). Selecting False on the filters shelf will keep only those with a non-null value.
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!