Tableau ZN

What is Tableau ZN function?

The Tableau ZN (short for Zero if Null) function returns a 0 if a function has a NULL value. Suppose you have a function with NULL values. These NULL values, if not dealt with, will cause problems and can jeopardize any meaningful predictions that you make with the dataset. It can be prevented using Tableau ZN.

In the example below, the Airbnb dataset has several hosts. Some hosts may not have enough data and may be represented as NULL. Using the ZN function, this can be managed.

With this, you can see the total number of bookings for each house type.

Note: This is most popularly used with the Tableau ZN Lookup junction.

What is Tableau ZN function?

The ZN() function in Tableau converts null numeric values to zero within the current context.
It’s commonly used to ensure accurate calculations and visualizations by preventing null values from affecting aggregates.
ZN() operates exclusively on numeric fields, making it unsuitable for text or date fields.
Avoid using ZN() on fields where null values have specific meanings that zero would misrepresent.
Tableau ZN helps maintain data integrity by ensuring calculations are not disrupted by null values.

Syntax

The syntax for the ZN function is:

ZN([expression])

Where [expression] is checked by the function and returns a 0 if there is a NULL value.

How to use the ZN function in Tableau?

Follow these steps to learn how to use the ZN function in Tableau.

Step 1: To start with the process, you will need to start the application and open a new Workbook. Open the application, then, in the toolbar, click on “File” – ”New.”

Step 2: Select the dataset of your choice by dragging and dropping it in the workbook.

You can also click on the “Data” tab or “Ctrl+D” to connect to a file.

Here, the general food consumption of different foods in Europe is used.

Step 3: Go to the first worksheet to start with your Tableau ZN function comparison.

Step 4: Select “Country” and “Biscuits.” Convert “Biscuits” to a discrete value by right-clicking on it and selecting “Discrete.”

As you can see, there is a NULL value in the table.

Step 5: Create a calculated field to normalize the dataset and remove all NULL values.

Step 6: Name the calculated field and use the ZN function on the “Biscuits” feature.

Step 7: Apply the calculation and place it in the “Text” component in the “Marks” tab.

It is the comparison table with and without the ZN values.

Tableau Zn - Use - Step 7 - comparison Table

From this, you can learn that if the ZN function isn’t used correctly, you will get the problem of the Tableau ZN not returning zero.

Examples

Go through the following examples to find unique ways to implement the Tableau ZN function in your data analysis.

Example #1: ZN Function for aggregated number

Suppose that you want to find the percentage of reviews that are accurate to the number of reviews per month in an Airbnb. For this, the ZN function can be used to prevent any NULL values from showing as a result of a lack of data. One must be prepared for all possibilities.

Step 1: Open a new workbook. Import a new database by dragging and dropping the Airbnb dataset of Austin, Texas.

Step 2: The “Host Id” is placed in the “Rows” component.

Step 3: Create a calculated field to find the number of reviews which are legitimate and can be verified.

Step 4: Use the ZN function with an aggregate function SUM to calculate the number of reviews per month to the accuracy of these scores.

Step 5: Drag and drop the calculated field to the “Rows” component.

It is the table with the Tableau ZN function used.

Tableau Zn - Example 1 - Step 5 - Table with ZN

It is the table without the ZN function.

Tableau Zn - Example 1 - Step 5 - Table without Zn

Example #2: ZN Function with IF statement

When you want to find the sales percentage of a certain category and just find the sales difference of the others, the IF statement can be used in conjunction with the ZN function to simulate the IF condition.

Using the Tableau Lookup function, the Sample-Superstore dataset is used to find the sales percentage and the difference between different sales.

Step 1: Create a new workbook. Import the Sample-Superstore dataset to the workbook.

Step 2: Drag and drop “Ship Mode” and “Segment” in the “Rows” component.

Step 3: Create a calculated field to create Sales Categories. It is as shown in the image below.

Step 4: Write this code to categorize the Sales into different categories.

Step 5: Create another calculated field.

Step 6: Use the ZN function with the IF condition to find the Sales percentage difference for those with “Excellent” and just see the difference in sales for the others.

The ZN function is used to convert all of the NULL values to 0.

IF [SalesCateogry_if] = “Excellent” THEN ZN(ZN(SUM([Sales]))-LOOKUP(ZN(SUM([Sales])),-1))/ABS(LOOKUP(ZN(SUM([Sales])),-1))

If the Sales are categorized as “Excellent,” then the previous sales value and current sales value are subtracted and divided by the previous value. The ABS function in Tableau is used to convert negative values to positive values, if any exist.

ELSE ZN(ZN(SUM([Sales]))-LOOKUP(ZN(SUM([Sales])),-1))

For all the other conditions, the current sales value is subtracted from the previous sales value.

Step 7: After applying the changes, place the “SalesCategory” calculated field in the “Rows” component.

Step 8: Drag and drop the Sales Percentage calculated field into the “Text” field in the “Marks” tab.

It is the final table.

Example #3

Suppose you want to compare and contrast two foods consumed by different countries. A roadblock may occur when there are null values in the features, preventing data visualizations in Tableau from being formed properly.

When you want to compare the amount of tea consumed in comparison to the use of sweeteners by different countries in the EU, you can follow the steps below.

Step 1: To start with the problem, open a new workbook and then drag and drop the Food Consumption in Europe dataset.

Step 2: Place “Country” in the “Columns” component and the “Tea” and “Sweetener” in the “Rows” section. You can see that there are null values in the “Sweetener” feature.

It may be because the dataset itself has blank values. What solutions do you have in this case? Can you add the mean value? This is not possible since each country is demographically distinct from the others. It means that we need to turn any null values to 0 instead. It will make the data visualization even smoother.

Step 3: To solve this, create a calculated field to use the ZN function on the “Sweetener” feature. Complete it as shown below.

Step 4: Call the ZN function on the “Sweetener” feature and apply this calculated field.

Step 5: Place the calculated field instead of the original “Sweetener”. Delete it by right-clicking on it and select “Remove.”

Create a dual axis between “Tea” and “Sweetener.”

Step 6: Change the graphical representation in the “Marks” tab to “Line.”

Step 7: Synchronize the axis values by right-clicking on the vertical axes and selecting “Synchronize Axis.”

It is the final graph.

From this graph, you can infer that European citizens take comparatively minimal amounts of sweeteners with their tea.

Important Things To Note

Use ZN() when you need to convert null numeric values to zero in Tableau to ensure accurate calculations and visualizations.
The Tableau ZN() function is useful for handling null values in numeric fields, preventing them from affecting aggregates and calculations.
ZN() ensures that calculations involving numeric fields do not break or return unexpected results due to null values.
Don’t use ZN() on non-numeric fields, as it is specifically designed for numeric data and will not work with text or date fields.
Avoid using ZN() indiscriminately on fields where null values have a specific meaning that zero would not accurately represent.

Frequently Asked Questions (FAQs)

1. Can I use ZN in Tableau with non-numeric fields?

No, ZN in Tableau is used only with numeric fields. ZN converts nulls to zero, which only applies to numeric fields, as zero is not a valid value for non-numeric data.

2. Does ZN in Tableau affect the underlying data?

No, ZN in Tableau does not affect the underlying data; it only changes the displayed value.

3. Can I nest ZN function in Tableau?

Yes, you can nest the ZN function in Tableau to handle multiple conditions, ensuring all null values are converted to zero.

Example: LOOKUP(ZN(SUM([Sales])),-1)

4. What happens if I use ZN in Tableau on a non-nullable field?

If you use ZN on a non-nullable field in Tableau, it has no effect, as there are no null values to convert. However, using it in calculations in Tableau may result in some errors where the Tableau ZN not working error comes in.

Tableau ZN