**Published:**31 Jul 2014

**Last Modified Date:**27 Jun 2019

### 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.

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.

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,**

`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:

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.

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.

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

Discuss this article... Feedback Forum

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

##### Related Links

Replace 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

POWER() Function Returns NULL When Taking Odd Roots of Negative Numbers

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

POWER() Function Returns NULL When Taking Odd Roots of Negative Numbers