KNOWLEDGE BASE

Calculated Field Returns Null Results


Published: 31 Jul 2014
Last Modified Date: 27 Jun 2019

Issue

Calculated fields return NULL or blank values instead of expected results

Environment

Tableau Desktop 

Resolution

CLICK TO EXPAND SOLUTION
Scenario 1: Expressions containing a field with a NULL value may return NULL values
he expression [Sales]+[Profit] will return NULL for any record in the underlying data where one or both measures is NULL. The expression SUM([Sales])+SUM([Profit]) may also return NULL, even though aggregations like SUM() ignore NULL values, if the entire sum is NULL.

Solution: Wrap the fields or aggregations in ZN()
The ZN() function converts NULL values into zeros.

[Sales] + [Profit] would become ZN( [Sales] ) + ZN( [Profit] )

SUM( [Sales] ) + SUM( [Profit] ) would become ZN( SUM( [Sales] )) + ZN( SUM( [Profit] ))
CLICK TO EXPAND SOLUTION
Scenario 2: Conditions containing a NULL value will return NULL values
Any condition that reference either NULL will return NULL. For example, [Region] = NULL will always return a NULL value.

Solution: Use ISNULL() or IFNULL()
The ISNULL() function will check if a field or literal value is NULL and return True or False. The IFNULL() function will replace NULL values with a specified value.

[Region] = NULL would become ISNULL( [Region] )
CLICK TO EXPAND SOLUTION
Scenario 3: CASE or IF statements without ELSE statements will return NULL values when no conditions are True
Tableau reads through each condition until one is True, and then returns that value. If no conditions are True then Tableau will return the value in the ELSE statement. If there is no ELSE statement, then Tableau will return NULL. For example, the following calculation will always return NULL because "North Pole" is not a region in the sample Superstore data set:
 
IF [Region] = "North Pole"
THEN [Sales]
END
 
Option 1: Change the condition so that it will return True. For example,

IF [Region] = "Central"
THEN [Sales]
END


Option 2: Add an ELSE statement to return something other than NULL. For example,

IF [Region] = "North Pole"
THEN [Sales]
ELSE 0
END
CLICK TO EXPAND SOLUTION
Scenario 4: Expressions including multiple mutually exclusive CASE or IF statements may return NULL values
Calculations that compare IF or CASE statements, or contain multiple conditions, that are never True at the same time may return NULL. For example, the following example using Superstore data will always return NULL because there is no record in the underlying data in which both IF statements return a non-NULL value at the same time: 
IF [Region] = "Central" THEN [Sales] END
-
IF [Region] = "West" THEN [Sales] END

Solution: Modify the calculations so all expressions return non-NULL values

Option 1 

Wrap each IF THEN statement in an aggregation, such as SUM():
SUM( IF [Region] = "Central" THEN [Sales] END )
 - 
SUM( IF [Region] = "West" THEN [Sales] END )

Note: this option will not work if any of the dimension(s) used in the conditional statement are included in the view. The above example will not work if [Region] is included in the view.


Option 2

Wrap each aggregated IF THEN statement in a Level of Detail (LOD) expression. For example:
{ FIXED : SUM( IF [Region] = "Central" THEN [Sales] END ) }

-

{ FIXED : SUM( IF [Region] = "West" THEN [Sales] END ) }

 

Note: It may be necessary to add dimensions from the view to the above calculation (after FIXED).

Option 3

Wrap each aggregated IF THEN statement in a table function. For example:
WINDOW_SUM( SUM( IF [Region] = "Central" THEN [Sales] END ))
- 
WINDOW_SUM( SUM( IF [Region] = "West" THEN [Sales] END ))
For another example using table functions, see Calculating Difference Between Two Values of the Same Measure in a View.
CLICK TO EXPAND SOLUTION
Scenario 5: Converting a non-numeric string into a number
A string (aka text value) that contains non-numeric characters will return NULL when converted into a number.

Solution: Modify the string to remove non-numeric characters
For example, if the field [Price] contains the string "$4.50" then the following calculation will return NULL:

FLOAT( [Price] )

If every value in [Price] has a preceding '$', then the calculation could be modified to

FLOAT( RIGHT( [Price], LEN( [Price] ) - 1 ))

If [Price] contains some string values with a '$' and some without, then the calculation could be modified to

FLOAT(
IF ISNULL( FLOAT( LEFT( [Price],1 )))
THEN RIGHT( [Price], LEN( [Price] ) - 1 )
ELSE [Price]
END

)
CLICK TO EXPAND SOLUTION
Scenario 6: Expressions dividing by zero will return NULL
Any number divided by zero will return a NULL value in Tableau.

Solution: Change the denominator to another value.
CLICK TO EXPAND SOLUTION
Scenario 7: DATE() or DATEPARSE() may return NULL
The functions DATE() or DATEPARSE() may return NULL if the date format of the text value is not supported by the computer's locale. For more information, please see Convert a Field to a Date Field

The function DATEPARSE() may also return NULL if the specified date format does not match the date format of the field. For example, the field [Order Date] contains values formatted as m/d/yyyy such as "1/13/2018". The calculation DATEPARSE('m-d-yyyy', [Order Date] ) will return NULL values because it uses dashes instead of slashes.

Solution: Change the date format to match the date format of the data exactly

Cause

  • For scenarios 1, 2 & 4, any calculation that includes NULL will return NULL because NULL values are not numeric values and thus require special handling.
  • For scenario 3, Tableau adds ELSE NULL to any logical calculation that does not have an ELSE statement. This means if all conditions are False, then NULL will be returned.
  • For scenario 5, any number divided by zero is mathematically undefined. For consistency, Tableau displays the value as a NULL. For more information, see this Wikipedia article Division by zero
Did this article resolve the issue?