5. Dealing with Null Values

Overview

In most data sets, Null values will often appear in certain columns. In this lesson, we will learn how to deal with Null values in Tableau, using the ZN() function to fix some calculations.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

Dealing with Null values

- Null values are blanks that often appear in certain database columns
- They can either be intentionally blank (representing zero) or incomplete data
- If they are intentionally blank, it's best to convert nulls to zero using the ZN() function
- This helps avoid some potential mistakes in your calculations
- The ZN() function returns a number if a number is present but zero if a null is present

Transcript

Up to now, we've been operating with a clean data set without any formatting errors or null values in the dataset. However, this is not always the case and it's likely your data will often contain some null values that will need to be dealt with.

Null values can often lead to mistakes in calculations and as a result, this lesson is critically important. I've included an additional product Claxerane that has some null values in it's sales figures. Let's go to the data source to see some of these values. So I'll scroll down to Claxerane. I have some rows that include a quantity but no sales value.

In Excel these blank cells are creating the null values that we see in Tableau.

When you see null values in a dataset like this, the one question you must ask yourself is, are these no the values an error, or do they represent a zero? In this example, we have quantity, but no sales. Which means the company is either giving away the product for free to certain customers, or these values are an error.

Let's assume for a second, that this is not an error and that Claxerane was sold to numerous customers at no cost under some trial basis. Let's go back to our sheet to see what impact this might have. If we assume that the data is correct then the sales per product is going to be correct because the null values will simply be left out of the Claxerane sum of sales calculation.

In the next chart where we calculate average price per unit again, we don't actually have an error because in our formula, we simply take the sum of sales and divide by the sum of quantity. In this scenario, the null values do not register in the sum of sales and the sum of quantity works correctly. So the average price per unit we get for Claxerane 1.3 to seven is correct. So for aggregated calculations nulls sometimes do not affect the accuracy of your answer. However, problems begin to arise when you use calculated fields to create a new column rather than make an aggregate calculation. And my example here will be price per unit.

In the price per unit calculation we divide the sales column by the quantity column.

And for Claxerane we have null values in the sales column.

Returning to my data source, I can see if I scroll down to Claxerane the null values in sales, resulting in null values in price per unit. And so when null is divided by a quantity it is considered null.

To fix this I could simply set the null value to zero. And in Tableau, we actually have a function to do this called ZN.

So let's return to our formula and go to edit.

And in our formula I'll wrap ZN function around sales.

And the ZN function is quite easy to understand if the value is not null, then ZN simply returns the value. And if the value is null, it will return a zero. So when you have no values in your dataset that you are confident represent zeros I would always use the ZN function to ensure that no null values affect your calculations. So now I press Okay. And when I return to my data source, and again, I'll scroll down to Claxerane and when sales are equal to null my price is equal to zero, whereas before it was equal to null while the ZN function helps you out when your null values actually represent zeros, if your null values represent errors, you must either remove these values from the data set, using say a data source filter, or obtain the correct information and refresh your data set. If you choose to use a data source filter to remove the null values then be sure to caveat your results when presenting them to your manager or client.

>
Formulas in Tableau
Further Formulas in Tableau

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial