How to handle Tableau Null values using Null Functions? Simplified 101

Data Analysis is a good way of extracting insights from data. It helps individuals and businesses to extract hidden information from data. This information can then be used for decision-making purposes. A business that makes decisions based on evidence has an advantage over its competitors. 

There are many Data Analysis tools. Tableau is a common Data Analysis tool among data analysts. Tableau users can extract various insights from their data to understand it in-depth. Tableau users can also create dashboards and visualizations from their data. When analyzing or visualizing your data with Tableau, you will encounter null values. The Tableau null values can be blanks that represent zero or incomplete data. 

These can have a negative effect on your Tableau calculations, thus, they must be dealt with. The good thing is that Tableau comes with a number of functions to help you deal with null values. In this article, you will learn about Tableau Null functions, what are different types of Tableau Null functions, and how they help Data Analysts.

Using Tableau Null Functions to Handle Tableau Null Values

Now that you have understood Tableau. In this section, you will learn about Tableau Null functions and read about different types of Tableau Null functions.  You can use Tableau Null functions to identify and even change null values. Specifically, the Tableau change null to 0 approach involves leveraging these functions to determine whether a value is null and, if so, converting it to 0 or another specified value. The Tableau Null functions help you to determine whether a value is null or convert the value to something else.

Users use Tableau Null functions to perform exploratory Data Analysis and clean the data. There are mainly 3 types of Tableau Null functions. The following Tableau Null functions are listed below:

• ISNULL
• IFNULL
• ZN

1. ISNULL

• ISNULL is the Tableau Null function that helps you to know whether a value is null or not. This Tableau Null function has the following syntax:

ISNULL([Field])

• ISNULL is a boolean function, meaning that it returns a True or False. If the value is null, it returns True while if the value is not null, it returns False. The function is mostly used with the IF function. However, you can still use it alone, for example, as a filter calculated field. 
• To use this Tableau Null function, you simply have to pass the name of the field to it as an argument. It is used for numeric fields, both aggregated and unaggregated. 

Let’s demonstrate how to use this Tableau Null function. You will use the following table that shows the amount of revenue generated from the sale of different products:

From the above table, you can see that some values for the “Revenue” field have nulls.

Let’s use the ISNULL function to determine the null rows.

• You will create a calculated field by clicking the dropdown arrow located under the “Data” tab and choosing “Create Calculated Field…”, as shown in the image below.

• Give the  calculation the name “ISNULL” and type the following function in the working field:

ISNULL(Revenue)

• Click on the “OK” button. 

• The new calculated field will be added to measure names under the “Data” tab. Double click it and see it work.
  A new column will be added to the table as shown in the image below:

• The ISNULL column has been added to the table. The field only has boolean values describing whether the value of Revenue for each row is null or not. Where revenue is null, the field shows a True. Where the field is not null, it shows a False. 

• The ISNULL function can be used together with the IF function. You have to use a 0 (Zero) where revenue is null and the value of the revenue where it is not null. 
  You can use the following calculation for this:

IF ISNULL([Revenue]) then 0 ELSE [Revenue] END

• When executed, the above calculation will return the following result:

• Where the value of revenue is 0, the ISNULL function returns a 0. Where revenue is not null, it returned the value of revenue. 

2. IFNULL

• The IFNULL function is just a combination of the IF and ISNULL functions. It is mostly used to change null values to something else. It is the short form of IF ISNULL() THEN … END statement. 
• You can use this function to convert null values to a static value or to a value from another field. It works with different data types, including aggregate and non-aggregate values. The function takes the following syntax:

IFNULL ([Field], [FieldOrValueSameDataType])

• Let’s demonstrate how to use this Tableau Null function. Replace nulls in the revenue column with 0:

IFNULL([Revenue],0) 

•  The calculation will return the following result:

• Where there is a null, the function returned a 0, otherwise, it returned the value of revenue. 

3. ZN

• The Tableau ZN function only works on numeric fields and it changes nulls to 0. It can be applied to both row-level and aggregate numbers. It takes the following syntax:

ZN([Number])

• The same syntax should be applied to aggregate numbers:

ZN(SUM([Number]))

• If you apply the function to date, string, or boolean data type, it will return an error. The following calculation shows how to apply this function to the Revenue column:

ZN([Revenue])

• The calculation will return the following result:

• Where the value of revenue is null, the function returned a 0, otherwise, it returned the value of revenue. 

That’s it you have read how to use different types of Tableau Null functions.

When do Blanks need to be replaced with Zeros?

There are three main scenarios that may create blanks that would need to be replaced with zeros:

NULL data

This happens when there are entries in the underlying data set, but the measure value or values are NULL or blank. In the Excel file “NULL and Missing Examples.xlsx” that is attached, the “In-Progress” and “Completed” fields for the project “Brosnya” are NULL. To tackle such scenarios, implementing a Tableau null to zero transformation can be beneficial.

Missing data

This occurs when there are no records in the underlying data set. The projects “In-Progress” and “Completed” for the project “Hibagon” are absent from the sample data.

Data filtering

The workbook that is attached has a [Date] filter that removes the entries for the projects “Started” and “In-Progress” for the “Dalmatian” project. Every step of the “Dalmatian” project contains records in the underlying data source.

Zeros will be used in place of NULL data for all parameters. Zeros will be used in place of missing data in options 3, 4, and 5. The filtered out data will be replaced with zeros by options 2, 3, and 4.

The Analysis > Table Layout > Show Empty Rows/Columns option may let you to see more rows or columns in the view; however, no data can be written into these rows.

In the formatting settings, all NULL values can be changed if the crosstab has only one measure. View Enter null values in format.

HOW TO COUNT NULLS IN TABLEAU

Use the ISNULL function to change each Null to True and each “not Null” to False in order to count Nulls in Tableau.

Due to its boolean nature, ISNULL can return either True or False. True is converted to 1 and False to 0 when a boolean is converted to an integer using the INT function. It is easy to sum the True and False once they are numbers. Thus, the following is the formula in Tableau to count Nulls:

SUM(INT(ISNULL([Field])))

Additional Reads:

• Tableau Table Calculations 101: Functions & Examples
• How to Build a Tableau Heatmap(Density Marks)
• Tableau Action Filters Simplified 101

Conclusion

In this article, you learned that Tableau Null functions help in dealing with null values to avoid issues caused by null values within data.

The Tableau ISNULL function checks whether a value is null or not. It returns a boolean result. The Tableau IFNULL function is used to change the value of nulls to something else. It works well with different data types. The Tableau ZN function changes null values to 0 (zero). It only works on numeric values. 

Before analyzing data from different sources, it needs to be loaded into the Data Warehouse to get a holistic view. Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

Frequently Asked Questions