If you have worked with Tableau, you may seen ‘null’ values appearing in your data. In this complete guide to Tableau and null values we will cover following topics.
Table of Contents
- Why Null values appear in the Tableau?
- What is the difference between null dimensions and measures?
- How to handle null values?
- How to count, replace, and exclude null values?
What are null values?
Null values means values that do not exist. Null value means absence of value. Here is a real-world example of why null values appear in data. Imagine that you go a farmers market one weekend. You see a volunteer sign-up sheet for a cause you care about. You decide to enter your information on the sign-up sheet.
The image above displays a few records from the sign-up sheet. Notice that the information is not consistent. We, humans, are anything but consistent. Some people have entered their full name, email, and phone. A few people chose to enter their first name and email. Phone number is not populate in the 2 of the 3 records. This absence of phone number will translate to null records.
Why Null values appear in the Tableau?
We saw how inconsistency in data input can cause null values to appear in your reports. There are other reasons besides data entry which cause null values. Here are some examples
- Incorrect join or an outer join with non-matching records.
- Technical issues in upstream data pipelines or bugs in the data warehouse.
- Field value not applicable.
Here is an example join-related issue. Let us say, you our doing join between two tables on location ID between dog and location tables. There is information about a dog in the dog table but the corresponding location is missing. This location will appear as null if you do a “left join”.
Data pipeline or warehouse-related are technical in nature. if your source has correct information. These are bugs in code.
Non-applicable values can be explained using an example. Let us take the example of student performance in the form of test scores. A report summarizing the annual performance should include all students. Students are included even if they missed one of the exams. However, if a question is asked “How many students took the first exam?”. You only want to include students who took the first exam of the academic year. A null record is valid for this record. The underlying reason is that the value is not applicable for this student.
What is the difference between null dimensions and measures?
Data can be categorized in dimensions and measures. What’s the difference between dimensions and measures? Dimensions describe the property or attribute of an object or person. You can count or you can perform mathematical operations on measures. There are some differences in how null values are handled in dimensions vs. measures. For non-additive metrics such as an arithmetic mean, replacing null value with a zero is ***not ***recommended.
-Sales by region (missing location dimension)
-Average temperate (missing measure (score) due to travel)
Factors affecting null values in metrics/measures.
Let’s say you’re located in New York. Average temperature in a yea fluctuates from -10 degrees (centigrade) to +35 degrees. You are a hobbyist meteorologist. You’ve been keeping track of the temperatures twice a month. You keep log of that temperature so that you know you can plot average temperature in a month or in a year. Let’s say you know you decide to travel to Europe and you will not be there to readings during January and February. The question is can you substitute zero for the missed measurements? And the answer is no. The average temperature in January/February can be can be below zero. If you substitute zero for missing measurements, your average temperature is going to move up. You need to be cognizant of the impact of substituting zero values.
How to handle null values?
There are only two ways to handle null records.
null value substitution — Example of null value replacement for a string field are: ” (empty string), ‘NA’, ‘Unknown’ etc. Some example of null value replacement for a measure is 0.
Exclude null value — Depending on the specific situation it may be acceptable to exclude null records. I do not recommend this as a preferred approach. What does not exist in front of your eyes can be hard to discover. Excluding null values can mask underlying problems.
How to count, replace, and exclude null values?
When working in Tableau, you will often be working with calculated fields. If a record contains null for a specific attribute, output of calculated fields using that attribute will also be null. In the next section, we discuss available function to work with null values.
How to use IFNULL, ISNULL, ZN function to work with null values?
Replace null value with an acceptable alternative and use this value in your calculated fields. For example,
if ( isnull([Country Name]) ) then "Not available" else [Country Name] end
You can then the above value in your calculated fields inside functions such as COUNT. You may also choose to exclude the value. Remember that excluding a value is equivalent to excluding a complete record.
The above IF-THEN-ELSE statement to handle null value can also written using the IFNULL function.
IFNULL([Country Name], "Not available")
ZN function is designed to handle number values. A field wrapped inside “ZN” “function will return zero when the values is value.
What Tableau features are available to work with null values?
In addition to calculated fields, Tableau gives some additional options to handle null values.
Special Values Indicator
When Tableau can plot a value, it displays a special indicator on lower right corner. You can either hide it or accept default value option. Please see screenshots below for additional details.
Alias
You can define aliases at the dimension or measure level. In this case, any null record can be replaced with user defined alternative. Aliases only apply to dimensions.
