How to Use ZN Function in Tableau

Dealing with blank spots in your data can be one of the most frustrating parts of building reports in Tableau. You're trying to create a clean table or a smooth line chart, but empty cells cause weird gaps and break your calculations. Luckily, Tableau has a simple function designed for exactly this problem: the ZN function. This article will walk you through what the ZN function is, why you need it, and how to use it step-by-step to clean up your dashboards.

What Exactly Is a NULL Value?

Before we can fix the problem, we need to understand it. In the world of data, a blank space often represents a NULL value. It's important to know that NULL does not mean zero. A zero is a number - it's a definitive value. NULL, on the other hand, means there is no value at all. It's an absence of information.

Think about it like this:

• If you check your bank account and have $0, you know your balance is zero.
• If the banking app crashes and can't display your balance, that's a NULL. You don't know the value - it could be anything, or nothing at all.

This subtle difference can cause big problems in your data analysis, which is where the ZN function comes in.

What Does the ZN Function Do?

The ZN() function is short for "Zero Null." Its job is straightforward: it looks at a number field, and if it finds a NULL value, it replaces it with a zero (0). If the value is a number already, it leaves it unchanged.

The syntax is as simple as it gets:

ZN([Your Measure])

For example, if you have a field called [Sales] that contains some NULL values, you can create a calculated field with ZN([Sales]) to convert all of those nulls into zeros, while leaving the existing sales figures as they are.

Why You Need to Handle NULLs in Your Data

So why can't we just ignore those blank spots? Letting NULL values linger in your data can wreck your reports in a few key ways.

1. NULLs Break Mathematical Calculations

This is the biggest reason to use the ZN function. When you perform mathematical operations, any interaction with a NULL value usually results in another NULL.

Imagine you have a straightforward calculation for profit: [Sales] - [Costs]. Let's say for one product, your data looks like this:

• Sales: $100
• Costs: NULL (maybe the cost data failed to import)

Your calculation becomes $100 - NULL, which Tableau evaluates to NULL. Your profit figure disappears completely, even though you had sales data. By wrapping the [Costs] field in the ZN function - [Sales] - ZN([Costs]) - the calculation becomes $100 - 0, resulting in a correct profit of $100.

2. NULLs Create Misleading Averages

When you take an average of a column, Tableau's AVG() function ignores NULL values entirely. It doesn't count them as zero, it simply pretends they don't exist. This can seriously inflate your averages.

For example, say you have sales figures for five salespeople: [100, 200, 150, NULL, NULL]

If you ask Tableau for the average, it will calculate (100 + 200 + 150) / 3 = 150. It completely ignores the two reps who had no recorded sales. A team manager might look at this and think the average performance is pretty good.

However, if the NULLs actually meant zero sales, then the real average is (100 + 200 + 150 + 0 + 0) / 5 = 90. That's a very different story! Using ZN() gives you control over whether you want to include these instances in your averages.

3. NULLs Cause Gaps in Visualizations

Blanks in your data often lead to visual awkwardness in your dashboards. They can cause:

• Gaps in line charts: If sales data for March is NULL, your yearly trend line will have a break in it instead of dipping to zero and back up.
• Missing bars in bar charts: A category with a NULL value simply won’t appear in your bar chart, which could lead people to assume it doesn't exist.
• Blank spaces in tables: A table or crosstab littered with blank cells is hard to read and looks unprofessional. Replacing them with zeros makes for a much cleaner and more understandable report.

How to Use the ZN Function in Tableau: A Step-by-Step Guide

Let's walk through a common scenario. Imagine you have a dataset of monthly sales for different product categories. For some months, certain categories had no sales, resulting in NULL values in your data.

Here's what your raw data might look like in a Tableau table:

That blank space next to "Homewares" in January and "Books" in February is a NULL. Let's fix it.

Step 1: Create a Calculated Field

In your Tableau worksheet, go to the top menu and select Analysis > Create Calculated Field... A dialog box will pop up, ready for your formula.

Step 2: Write the ZN Formula

First, give your new calculated field a descriptive name. Something like "Sales (Cleaned)" or "Sales with Zeros" is perfect. In the formula box, type:

ZN([Sales])

This tells Tableau to create a new field that takes the value of [Sales], but if [Sales] is null, it should use a 0 instead. The dialog should show "The calculation is valid." at the bottom. Click OK.

Step 3: Replace the Old Field with the New One

You'll see your new calculated field, "Sales (Cleaned)", in the Data pane on the left, usually under the Measures section. Now, replace the original [Sales] field in your visualization with this new one.

You can do this by dragging the "Sales (Cleaned)" field and dropping it directly on top of the SUM(Sales) pill in your view (on the Rows, Columns, or Text shelf). Tableau will automatically swap them.

Step 4: See the Results

Your table will now look clean and complete:

The NULLs have been replaced with zeros. Now your table is easier to read, and any downstream calculations you build using this "Sales (Cleaned)" field will be accurate.

ZN vs. IFNULL vs. IF ISNULL: Which to Use?

Tableau often provides multiple ways to accomplish the same task, and handling nulls is no exception. While ZN() is great, it's helpful to know about its cousins, IFNULL() and IF ISNULL(), and when to use them.

• ZN([Sales]): This is the specialist. It is specifically designed to replace a NULL with a zero. Nothing more, nothing less. It's the quickest and cleanest way to do this one specific job.
• IFNULL([Sales], 0): This function does the exact same thing as ZN([Sales]). It checks if [Sales] is null and, if so, returns the second argument (in this case, 0). It's essentially the long-form version of ZN. Use whichever you prefer, as their performance is identical.
• IF ISNULL([Sales]) THEN 0 ELSE [Sales] END: This is the most flexible and powerful of the three. It uses a logical test (ISNULL) to check for nulls and then allows you to define outcomes for both TRUE and FALSE.

When to Use Other Approaches

So, when would you use the more complex IF/THEN structure?

The answer is when you need to replace NULL with something other than zero. ZN only knows how to return a zero. But what if you want to replace a NULL sale value with the average sales of all other products? You'd use an IF/THEN logic.

Moreover, the IF/THEN structure works with dimensions (text fields), while ZN() only works on measures (numbers). For example, you can cleanse a text field like this:

IF ISNULL([Region]) THEN "Unknown" ELSE [Region] END

A Word of Caution: Is Zero the Right Answer?

While the ZN() function is incredibly useful, it's not a magic wand. Before you apply it across your entire dataset, take a moment to ask yourself an important question: does this NULL actually mean zero?

Sometimes, data is null for reasons other than a zero value:

• Data Entry Error: Someone simply forgot to enter the data.
• System Glitch: A failed data import or API call resulted in blank fields.
• Not Applicable: The value truly doesn't apply (e.g., the "Commission" field for a non-sales employee).

Force-fitting a zero into these scenarios can skew your analysis. If NULL means "we don't know," then replacing it with a definitive "zero" might be incorrect. Always try to understand the source of your NULLs before replacing them. Sometimes, the right move is to filter them out of your view or work with your data engineering team to fix the source issue rather than patching it over in Tableau.

Final Thoughts

The Tableau ZN function is a simple, effective tool every analyst should know. It helps you clean up your reports, ensure your calculations are accurate, and create professional-looking dashboards by elegantly handling the common problem of NULL values. By swapping out those disruptive blanks for clean zeros, you make your data more reliable and easier for everyone to understand.

Cleaning and preparing data is the essential first step to good analysis. Once your dataset is ready, the next step is building the reports and dashboards that lead to real business insights. Many of our users save hours each week by automating this part of the process. At Graphed, we let you connect your data sources and use plain English to build real-time dashboards almost instantly. Instead of dragging and dropping fields, you can just ask, "Show me last month’s conversion rate by campaign," and watch as the report builds itself in seconds.