How to deal with Null handling in Tableau or Is Not Null

What is a null value and why does it matter?

When you’re looking for ways to make your code more efficient and reliable, an important concept to master is the concept of null values.

A null value is a special value that represents the absence of a value or reference within most programming languages. It acts as a placeholder for when a value is missing or not yet known.

Depending on the language you are working with, and the tools you are using to develop, nulls may cause your data to behave in unexpected ways which can lead to inaccuracy and inefficiency.

How do null values behave in Tableau?

Null values may cause a variety of issues when working with data in Tableau. Some the most common errors that null values may cause in Tableau are:

• Inconsistent or Incorrect Results: Null values can lead to inconsistent or incorrect results when performing calculations or aggregations on data sets that contain null values. This is because null values are often treated from other values and can cause unexpected results when included in calculations.

  Example: Inconsistent Results

  

  Against items C, D and E there are null values in TestData3. Against item H there is a null value in the TestData3 column. If either column contains a null then any attempt to combine those columns using normal mathematical operators returns a null.

• Difficulty identifying missing data: Null values can make it difficult to identify missing data in a data set, as null values are often used to represent missing or unknown data. This can make it harder to understand the completeness of your data and identify potential data quality issues.

• Increased data size: Null values can increase the size of your data set, as they often require additional storage space. This can lead to slower performance and reduced scalability when working with large data sets.

Options for null handling in Tableau

First, consider how you want your data to behave. If you have nulls then you could set them to a placeholder value. However, if for example you set a placeholder value of zeroes and then average the column you will find that your average is dragged down by the zeroes. On the other hand, you may be quite happy to set your values to a default to enable other calculations to behave properly. Another option may be to use filters to exclude these values.

Setting Placeholder Values

• Using a ZN formula to set nulls to 0: The ZN function in Tableau is used to convert a null value to a zero value. The ZN function will go through your data set and replace any null values in the specified field or expression with zeros. The syntax for the ZN function is as follows:

ZN([SpecifiedField])

• Using an ifnull formula to replace nulls with a specific value: The IFNULL function allows you to replace null values with a specified value in your data set. Using the IFNULL function is super easy! All you have to do is specify the field or expression that contains the null values you want to replace, and then specify the value you want to use to replace the null values. For example:

IFNULL([SpecifiedField],0)

Identifying null values

• Using an isnull formula to return a boolean true/false: Simply specify the field you want to check for nulls and the isnull formula will return a true or false against every row, depending on whether the result is null or not

ISNULL([SpecifiedField])

• Using an isnull alongside an if formula to return a specific value if a column is null: You can use isnull wrapped in an if formula to return a specific value if a column is null. To do this open an if formula. The logical test portion of the if statement is isnull([SpecifiedField]) = True. The syntax is as follows:

IF ISNULL([TestData2]) = TRUE then 'This data is null' else str([TestData2]) END