Last Modified Date: 13 Nov 2023
Environment
Tableau DesktopResolution
CLICK TO EXPAND SOLUTION
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.
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.
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,
Option 2: Add an ELSE statement to return something other than NULL. 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:
Solution: Modify the calculations so all expressions return non-NULL values
Wrap each aggregated IF THEN statement in a Level of Detail (LOD) expression. For example:
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:
If every value in [Price] has a preceding '$', then the calculation could be modified to
If [Price] contains some string values with a '$' and some without, then the calculation could be modified to
)
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.
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
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
Additional Information
Force a 0.0 when there is no recordReplace NULL or Missing Data With Zeros or Existing Data
Data Aggregation in Tableau
Date Calculated Fields Return Nulls After Extract Refresh
Grand Totals Are Blank For Calculated Field That Include ATTR() Including Fields That Blend Data From Multiple Data Sources
IIF Function using ATTR Returns Unexpected NULL Results
Blending Data on Non-Matching Date Fields
Converting String to Date or Datetime Type Results in Null Value
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials