In Tableau, ZN(expression) returns the expression’s value when it is not null, and returns 0 when it is null. It is a numeric null-handling function: use it when a missing numeric value should be treated as zero, but not when “unknown” or “not applicable” needs to remain distinct from zero.
What ZN means in Tableau
Tableau uses null to represent the absence of a value. A null is not the same as the number zero: zero is a known numeric value, while null means there is no value available for that field in that record or calculation. The ZN function changes that distinction for the expression you give it by substituting zero when the result is null.
For example, if a row’s [Profit] value is null, ZN([Profit]) returns 0 for that row. If [Profit] is 125, the expression returns 125 unchanged. ZN does not fill in the source data or alter the underlying database; it changes the result of the Tableau calculation wherever you use it.
The name is commonly understood as “zero null.” The practical point is more important than the name: ZN is a specific way to make a numeric calculation return zero instead of null in the cases where the expression evaluates to null.
Syntax and basic examples
The syntax is ZN(expression). Put a numeric field, calculation, or numeric expression inside the parentheses. For instance, ZN([Sales]) returns the sales value when present and zero when it is null. A calculation can also apply ZN to an aggregate, such as ZN(SUM([Sales])).
In Tableau Desktop, create a calculation by choosing Analysis > Create Calculated Field, or right-clicking in the Data pane and choosing Create Calculated Field. Give it a name, enter the expression, and check the editor for errors before selecting OK. A calculated field such as Sales, Null as Zero with formula ZN([Sales]) can then be used in a view like another field.
Common uses include:
ZN([Discount])to treat a missing numeric discount as zero for a calculation.ZN(SUM([Returns]))to return zero when the aggregate expression is null.ZN([Actual]) - ZN([Budget])to calculate a difference with zero substituted for either null input.
These examples are appropriate only if zero is a defensible interpretation of the missing value. A calculation can be syntactically valid and still communicate the wrong thing if a null really means “not recorded.”
How ZN behaves with aggregates
ZN can be applied at different levels of a Tableau calculation. ZN([Sales]) is a row-level expression if [Sales] is a row-level field. ZN(SUM([Sales])) applies the substitution to the result of an aggregate. Tableau’s rules about mixing aggregate and non-aggregate expressions still apply: when a calculation combines fields at different levels, you may need to aggregate consistently.
It is also possible to write SUM(ZN([Sales])). This first replaces null sales values on individual rows and then sums the results. By contrast, ZN(SUM([Sales])) first computes the sum at the level of detail in the view, then changes a null result to zero. For a group that has rows with numeric sales, the sums will often be the same because adding zero does not change a total. The expressions are not interchangeable in every calculation, however: their order matters for other aggregations, and they operate at different stages.
For example, replacing nulls before calculating an average can change the result. If the ratings are 4, 2, and null, AVG([Rating]) averages the two present ratings and returns 3. AVG(ZN([Rating])) treats the missing rating as zero and averages 4, 2, and 0, returning 2. The second result may be useful if a missing rating truly means a score of zero, but it is misleading if the rating was simply not collected.
Be aware that a calculation cannot necessarily create a mark for a category or date that is absent from the view’s underlying data. ZN can replace a null result for an expression that is evaluated, but it does not invent missing records or automatically add every possible dimension value. Showing missing dates or categories is a separate question involving the view, data, and Tableau’s domain completion options.
When using ZN is useful
ZN is useful when your analytical rule explicitly treats missing numeric input as zero. One common case is a measure where no activity means no amount: for example, a product with no recorded returns during a period may need to show a return total of zero rather than a blank, provided the data model supports that interpretation.
It can also simplify arithmetic. A subtraction such as ZN([Actual]) - ZN([Plan]) returns a numeric difference even if one input is null. That can make a table easier to read and prevent null results from propagating through the calculation. But the choice affects meaning: if a missing plan is unknown, treating it as a plan of zero can make a variance appear much larger than the real, unavailable comparison.
Other reasonable situations include:
- Displaying a numeric measure as 0 instead of a blank when a zero is the intended business meaning.
- Combining optional numeric components in a total when omitted components should contribute nothing.
- Building a calculation that needs a numeric result for subsequent arithmetic and has a documented rule for missing inputs.
Consider whether the calculation is for display, analysis, or both. A zero that improves readability may still affect averages, counts, percentages, rankings, and downstream calculations. If the substitution is only a presentation choice, preserving the original null in analytical calculations may be safer.
ZN compared with IFNULL and ISNULL
Tableau provides other null functions, and choosing the right one depends on the replacement you need. IFNULL(expression, replacement) returns the expression when it is not null and the replacement when it is null. It is more flexible than ZN because you choose the fallback value, subject to compatible data types. For example, IFNULL([Region], "Unassigned") can provide text for a missing region; ZN is not the appropriate function for that job.
ISNULL(expression) tests whether an expression is null and returns a Boolean result: true if it is null and false otherwise. Use it when the calculation needs to identify missingness rather than replace it. For instance, a field based on ISNULL([Sales]) can flag records with missing sales for investigation.
- Use
ZN([Value])when a numeric null should specifically become zero. - Use
IFNULL([Value], replacement)when you need a different fallback or the expression is not numeric. - Use
ISNULL([Value])when you need to detect or label nulls rather than erase the distinction.
These functions do not determine why a value is missing. If nulls result from a join, incomplete collection, or a calculation, the right treatment depends on that cause and on the question the analysis is meant to answer.
Common mistakes and interpretation risks
The most important mistake is assuming that null always means zero. A null sales amount might mean there were no sales, but it could instead indicate a failed import or a record that has not been completed. Converting every such value to zero can hide a data-quality problem and turn uncertainty into a claim of no activity.
Another common issue is changing a statistic without realizing it. Replacing nulls with zero before an average, rate, or other calculation can alter the denominator or the values included. Similarly, counting a field after making previously null values non-null can change a count. Check what the original calculation includes and test a small example before applying ZN broadly.
Also distinguish a null measure from a missing mark. A blank cell in a crosstab may result from a null value, but it may also appear because there is no record for that combination of dimensions. ZN addresses a null expression; it does not by itself guarantee that Tableau will draw every absent combination. If a view should show every date or category, investigate the data and view settings as well as the calculation.
Finally, a visible zero and a blank are not merely formatting variations once ZN is part of a calculation. The value becomes numeric zero for calculations that use that field. Number formatting can affect how a value looks, but it does not undo the substitution or restore the original null.
How to decide whether to use it
Before adding ZN to a calculated field, identify what a null represents in the source and what the report’s audience should infer. If the source system distinguishes “no activity” from “not reported,” a blanket replacement may erase an important distinction. If both cases are currently represented as null, a better solution may be to fix the data model or create a separate status indicator.
A practical decision process is:
- Inspect several records where the field is null and determine why they are null.
- Write down the intended meaning of zero for this measure, including how it affects totals and averages.
- Choose the calculation level: replace row-level values, or replace a null aggregate result.
- Compare results with and without ZN in a small view, including an average or rate if those metrics are used.
- Label the field or document the rule so that viewers know that missing values have been treated as zero.
This check is especially worthwhile for dashboards used to monitor performance. A display that replaces blanks with zeros can look complete, but it can also make missing data appear like poor or absent performance. A tooltip, footnote, or separate missing-data indicator can preserve context.
Frequently Asked Questions
What does ZN do in Tableau?
ZN returns its numeric expression unchanged when it is not null and returns zero when it is null. It does not modify the original source data.
What is the difference between ZN and IFNULL in Tableau?
ZN specifically substitutes zero for a null numeric expression. IFNULL lets you specify a replacement value, such as a different number or compatible text value.
Does ZN turn every blank in a Tableau view into zero?
No. It changes null results for expressions Tableau evaluates. It does not necessarily create marks for categories or dates that have no corresponding data rows.
Should I use ZN for missing sales?
Only if a missing sales value means zero sales in your data context. If it means unknown, unreported, or unavailable, replacing it with zero can mislead readers and affect calculations.
What is the difference between ZN(SUM(field)) and SUM(ZN(field))?
The first substitutes zero after calculating the aggregate; the second substitutes zero on each row before summing. They can match for straightforward totals, but their calculation level matters, especially when using averages or other measures.
Related
- JavaScript split(): Syntax, Examples, and Common Mistakes
- Chemistry Balance Equation Worksheet: Practice and Answers
- Is the Balance of Payments Always Zero? Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Divide in Excel Without a Function: Use the / OperatorTo divide in Excel without a function, enter a formula that starts with an equals sign and uses…
- Excel Formula to Find Duplicate Names in a Name ListTo find duplicate names in Excel, use =COUNTIF($A$2:$A$20,A2)>1 beside a list in A2:A20, then fill the formula down.…
- Excel CONCAT and Text: Formulas, Examples, and FixesTo join text and cell values in Excel, use CONCAT or the ampersand operator. For example, if A2…
- Excel Formula for a List of Numbers: 10 Useful OptionsTo create a list from 1 to 10 in Excel, enter =SEQUENCE(10) in an empty cell; it spills…
- Excel Formula to Return Multiple Lookup ResultsTo return every row that matches a lookup value in Microsoft 365 Excel, use the FILTER function. For…
- Excel Formula for Listing Items: 5 Real MethodsThe fastest way to list items in Excel is to use the =UNIQUE(range) formula to extract distinct values,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.