Tableau Null functions: IFNULL, ISNULL and ZN

//

This post describes the Tableau functions designed to identify and change null values. These functions either identify if a value is null or they are used to convert null values to something else.

There are three functions in Tableau designed specifically to handle null values. Using functions in a formula is one of four different ways to remove null values in Tableau.

This post covers the following Null functions:

  • ISNULL
  • IFNULL
  • ZN

ISNULL IN tABLEAU

Use this function to know if a value is null or is not null. The syntax is simple:

ISNULL([Field])

It’s a Boolean function, meaning it returns True if a value is null and returns False if a value is not null. Almost always ISNULL appears within an IF function. There are situations where it does work alone – for example if used as a filter calculated field – but this is more unusual.

To use, enter the field into the ISNULL function, either aggregated or not aggregated – it accepts both.

IS NOT NULL in Tableau

To write IS NOT NULL in Tableau, use the ISNULL function with the NOT function.

In some coding languages IS NOT NULL is often written:

IF [FIELD] IS NOT NULL THEN A ELSE B END

In Tableau this doesn’t work. In Tableau, to write IS NOT NULL, use the NOT and ISNULL functions:

IF NOT ISNULL([Field]) THEN "do something when not null" ELSE "do something else" END

Of course, should you prefer to keep away from NOT, as an alternative there’s always the ELSE part of the IF statement for IS NOT NULL:

IF ISNULL([Field]) THEN "do something" ELSE "do something when not null" END

Both of the above IS NOT NULL options are effectively the same; the NOT just inverses the ISNULL statement, so it returns True when the value is not null.

A Tableau calculated field for IS NOT NULL
IS NOT NULL in Tableau

IFNULL in Tableau

The Tableau IFNULL function changes a Null value to something else. It’s a shorter way of writing an IF ISNULL() THEN … END statement.

This function works to convert Null dates, text and numbers to something else.

It can convert nulls to a static value. Also, it can convert nulls to a value from another field.

As well as working with most data types, IFNULL also accepts aggregate and non-aggregate values.

The main thing to be aware of; the data type coming out must match the data type going in and if the number going in is an aggregate, the number coming out must also be aggregated OR a static value.

So, if checking for a Null date, the Null replacement value also has to be a date. It’s not possible to replace a date with a dash, for example, using IFNULL, as a dash is a string data type. Therefore, this would error:

IFNULL([DateField],"-")

(Note – for this to work the DateField would also need to be a String data type. Convert the date to a string by wrapping in the STR() function. However, once the DateField is a String date type, date functions no longer with that field.)

There is a better way than using the Tableau IFNULL function to convert null dates. Using an alias works better – this article explains the options to convert a null date to a blank.

IFNULL syntax in Tableau

The syntax of Tableau IFNULL is:

IFNULL([Field],[FieldOrValueSameDataType])

For example, to replace a Null [Name] with “Unknown”, the formula is:

IFNULL([Name],"Unknown")

Or to replace a missing [EndDate] with today:

IFNULL([EndDate],TODAY())

Or to replace the blank end date with a static date:

IFNULL([EndDate],#2050-01-01#)

Using IFNULL with numeric fields, it works to replace both aggregated numbers and row-level numbers.

With a row level formula, to replace a Null [Amount] with 0:

IFNULL([Amount],0)

Then to get the total amount:

SUM(IFNULL([Amount],0))

It’s also possible to replace aggregate numbers using IFNULL. For example, if the sum of [Amount] is null, then replace it with the sum of [Sales]:

IFNULL(SUM([Amount]),SUM([Sales]))

Remember, if an aggregate is going into the function, if it’s another field as the replacement, that also has to aggregate. This would error:

IFNULL(SUM([Amount]),[Sales])

If replacing with a static number, there’s no need to aggregate on the way out. For example, this is valid:

IFNULL(SUM([Amount]),0)

IFNULL does works well with numbers, however, if the aim is to replace a Null number with zero, there’s a simpler way: the ZN function.

ZN function in Tableau

The Tableau ZN function only works for numeric fields and changes Null to 0. That’s the only use for ZN: to change Null numbers to zero. It works for both row level and aggregate numbers.

The syntax for row level is:

ZN([Number])

The syntax for aggregate numbers is very similar:

ZN(SUM([Number]))

This function will error if using it with String, Date or Boolean data types – or anything that’s not a number.

ZN is a quicker way of writing:

IFNULL([Number],0).

Once your Null is zero, if a requirement is to format zero as a hyphen, then it’s now possible.

When dealing with numbers, ZN is useful to understand. It’s far simpler than using IFNULL statements or combining IF statements with ISNULL.

Take care when replacing Null aggregate numbers.

When working with numbers, use caution. For example consider the following data set:

IDAmount
110
25
3 
425

The total amount is 40 and there are 4 items, in which case the average can interpret as 40/4=10.

However, the average of that in Tableau is 40/3=13.33. This is because the Null record doesn’t appear in the denominator.

The ZN function can resolve this. Note the position of the ZN function: it wraps the [Amount] field, converting Null amounts to 0, then calculates the average.

The following formula returns 10:

AVG(ZN([Amount]))

But if the ZN is in a different place in the formula, the result can change:

ZN(AVG([Amount]))

Notice the ZN wraps the AVG calculation. This formula returns 13.33.

Do take care of the position of ZN in calculated fields as it can have a big impact on the result.

NULL functions to count Nulls in Tableau

To count Nulls in Tableau, use ISNULL to convert each Null to True and each not null to False.

ISNULL returns either True or False. Converting a boolean to a number using the INT function converts True to 1 and False to 0. Therefore, the formula to count nulls in Tableau is:

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

Wrapping up the Tableau Null functions

Knowledge of these 3 functions makes manipulating nulls in Tableau very simple. Just pay attention with the aggregated and row-level numbers.

Leave a Comment