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 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.

Table of Contents

• Prerequisites
• What is Tableau?
• Key Features of Tableau
• Using Tableau Null Functions to handle Tableau Null Values
  • Tableau Null Function: ISNULL
  • Tableau Null Function: IFNULL
  • Tableau Null Function: ZN
• Conclusion

Prerequisites

• A brief knowledge of Tableau.

What is Tableau?

Tableau is a Data Analysis and visualization tool used in the BI industry. It helps to make raw data simple to understand. With Tableau, professionals across all levels of organizations are able to understand data. It also helps non-technical users to create custom dashboards from their data. 

Tableau makes Data Analysis faster and its visualizations are in the form of worksheets and dashboards. You don’t need programming skills to use Tableau, and that is why it has attracted interest from people in different sectors including business, researchers, and more. 

Key Features of Tableau

Some of the main features of Tableau are listed below:

• Bridge Multi-Pool: Tableau supports Multi-Network connectivity using online schedules. Organizations can view and understand their private network data in Tableau Online.
• Tableau Exchange: Tableau offers a gallery for Tableau Extensions that allow users to jump-start their Data Analysis.
• Live and In-Memory Data: Tableau supports connectivity with live data sources and even data extracted from external sources as in-memory data.

To learn more about Tableau, click here.

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. The Tableau Null functions help you to determine whether a value is null or convert the value to something else. To know more, click here.

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.

Conclusion

In this article, you learned that Tableau is a data analytics tool. It comes with different visualizations to help its users present their data visually on dashboards. 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. 

Visit our Website to Explore Hevo

Companies need to analyze their business data stored in multiple data sources. The data needs to be loaded to the Data Warehouse to get a holistic view of the data. Hevo Data is a No-code Data Pipeline solution that helps to transfer data from 100+ data sources such as Tableau to desired Data Warehouse. It fully automates the process of transforming and transferring data to a destination without writing a single line of code.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about Tableau Null Functions in the comments section below!