Dealing with NULLs

When working with calculated fields, it is important to understand what NULLs are and how to deal with fields that have NULL values.

A NULL is a missing value. It is an indeterminate value. A NULL is not:

• a zero

• a space

• an empty string

Whenever there is an operation between a field that has a value and a field that has a NULL, the result is always a NULL. When you add 1 to nothing, what is the result? Indeterminate. When you concatenate a first name to an unknown value, what is the result? Indeterminate—we don't know.

Let's take the following data set:

Once you connect this Excel file to Tableau, the initial connection screen will look like the following. Notice how the missing values in Excel appear as NULL in the preview screen:

The following formula will calculate the discount amount correctly if there are no NULL values:

If we try to calculate the total discounted price as normal, using the following formula, we are going to get incorrect results:

Here are a few ways to deal with NULL values:

• The IFNULL value:

  

• The IIF and ISNULL value:

  

• The IF…ELSE and ISNULL value:

  

• The ZN value:

  Note

  Note that ZN stands for Zero if null, which means this function will only work if the expected return value is numeric.

  

• The CASE value:

  

Each of these calculations will produce a 0 for the discount amount if the discount percentage is missing. This will allow the discounted sales amount, that is, sales less discount amount, to be calculated correctly: