There are four ways to hide Null values in Tableau:
- Format
- Filter
- Formula
- Alias
The first is to Format the nulls, second is to Filter out the null values, and third handling nulls with a Formula. Finally, in some circumstances, an Alias can change a Null to something else
This article will describe:
- how to use formatting to handle null values
- how to remove null values with a filter, both discrete and continuous nulls
- Use an Alias to change Null to something else
- how to exclude null values in a Tableau calculation, including the functions to handle null values, ZN, IFNULL and ISNULL
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, an Alias, 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 remove continuous (green) Null values
To filter out continuous null values, put the continuous pill, for example SUM([Sales]), to the filter shelf. Remove 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 remove discrete (blue) Null values
To filter Null dimensions / discrete measures (blue pills), 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 or alias to replace the Null values.
Alias – alter the Alias to change the Null value
The values in a dimension can alias to something else. It is possible to set the aliases for dimensions of string, numeric and boolean data types.
Setting an alias is the simplest way to change a null to something else.
To alias the values in a field, right-click on the dimension in the list of dimensions. As long as it’s not a Date data type, in the list of menu options find Aliases.
Click Aliases and set the value to change Null to something else.
Formula – Handle Null values using a Formula
There are three Tableau functions to handle Null values:
Use these to remove null values in a calculated field.
The Tableau ZN function
The Tableau ZN function converts Null numeric values to 0. Therefore, ZN is the best option to replace Null with 0.
ZN can handle both aggregate and non-aggregate numbers.
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:
[ZN]: ZN([NumericField])
And with an aggregate:
[ZN]: ZN(SUM([NumericField]))
IFNULL in Tableau
The IFNULL function is a simple way to replace a Null value with 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.
The syntax of IFNULL in Tableau is:
IFNULL([Field-To-Test],[Null-Replacement])
The Null replacement can either be a value entered into the formula or it can be another data field.
The IFNULL function accepts measures and dimensions, and it can also handle aggregate and non-aggregate measures.
An important point about IFNULL in Tableau, 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.
Also if one side of IFNULL is aggregate, if replacing with a value from another field, the other side also has to be an aggregate. It is possible to replace an aggregate with a static number without an error.
ISNULL in Tableau
ISNULL differs to the other formulas. It tests if a value is Null. It doesn’t convert a Null to another value.
The Tableau ISNULL 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
A benefit of using ISNULL in an IF statement is additional flexibility. The example below shows one method to replace a null date with some text:
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.
The best way to remove Null values depends on your data
Now you have options to handle null values. Which works best for you depends on your specific requirements and your data.
There you have it, the main ways of hiding null values in Tableau!
I have a viz that displays profit by regions N, S, W, E (rows) and product (columns). I set a filter on profit to only show profits less than zero, that is, losses. So within each region, I see either a graph that shows a loss or nothing when a region/product combination does not have a loss. That part works. The problem is that I have one region with no losses. This region does not display at all. I lose the information that there is a regions with no losses. Is there a way to get the region to display even though all values within it are filtered?
I haven’t spent long looking at this but it could be something to do with filtering out the underlying data when what you actually want to do is only filter the data on display.
When filtering a standard field the entire underlying dataset is filtered. When filtering a table calc only the data on display is filtered. From your brief description this could be the solution for your problem – convert your measure (profit/loss) into a table calculation and filter this new calculated field.
Read this article on filtering table calculations for further details should you need them.
If that doesn’t help please send me the workbook to look into this properly.
Hi ,
Below is my query, I am executing the below query in tableau
in below query if promo code is there for customer the it will populate as present, if not it will populate ‘not present’
my question is that if no one has promo code then it will populate has blank in my bucket in tableau
can any one help me on this how to get this possible
SELECT loan_id,
CASE
WHEN lr.promo_code IS NULL THEN ‘Not Present’
ELSE ‘Present’
END AS promo_code_presence,
FROM loan_request lr
I don’t understand the question, can you please rephrase or give an example?
But instead of that it populating only ‘present’ its not showing ‘not present’
Thanks a lot!