KNOWLEDGE BASE

IIF Function using ATTR Returns Unexpected NULL Results


Published: 12 Oct 2016
Last Modified Date: 14 Oct 2016

Issue

When using ATTR() in the conditional statement in an IIF() function, then the results are sometimes Null depending on the level of detail in the view.

Environment

Tableau Desktop

Resolution

Option 1: Use MIN() instead of ATTR()

For example, the following calculation:
IIF( ATTR( [Regions] ) = "Central", SUM( [Sales] ), SUM( [Profit] ))

Could become:
IIF( MIN( [Regions] ) = "Central", SUM( [Sales] ), SUM( [Profit] ))

Option 2: Specify what the results should be when the conditional statement returns NULL

For example, the following calculation:
IIF( ATTR( [Region] ) = "Central", SUM( [Sales] ), SUM( [Profit] ))

Could become:
IIF( ATTR( [Region] ) = "Central", SUM( [Sales] ), SUM( [Profit] ), SUM( [Quantity] ))

Cause

The IIF() function allows the user to specify specific results for when a conditional statement evaluates to TRUE, FALSE, or optionally NULL. When the conditional statement evaluates to NULL and no result is specified then the expression will return NULL.

The ATTR() function returns a NULL value if the first value in a partition does not match the last value. For example, ATTR( [Region] ) will be NULL unless [Region] is in the view. When part of a conditional statement is NULL then the whole conditional statement evaluates to NULL.
Did this article resolve the issue?