Calculated Field Returns Null Results

Scenario 1: Expressions containing a field with a NULL value may return NULL values

The 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] ))

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] )

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

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.

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
)

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.

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