When working with calculated fields, it is important to understand what NULL
s 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:
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:
IFNULL
value:IIF
and ISNULL
value:IF…ELSE
and ISNULL
value:ZN
value: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:
3.138.36.38