KNOWLEDGE BASE

Calculations with multiple IF THEN statements return NULL results


Published: 10 Dec 2015
Last Modified Date: 09 Mar 2018

Issue

When a calculation includes or references multiple IF THEN or CASE WHEN statements, then the results might be NULL. For example, the following example using Superstore data will always return NULL: 
IF [Region] = "Central" THEN [Sales] END
-
IF [Region] = "West" THEN [Sales] END

Environment

Tableau Desktop

Resolution

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

Cause

This issue occurs because the conditions are mutually exclusive, and the overall calculation will return NULL if any part of the calculation returns NULL.

Non-aggregated IF THEN or CASE WHEN statements are computed for each record in the underlying data set. Because there is no record in the underlying data that is true for both [Region] = "Central" and [Region] = "West" at the same time, then one IF THEN statement will always return NULL.
 

Additional Information

Option 1, wrapping each IF THEN statement in an aggregation, works because Tableau Desktop will first compute each IF THEN statement for every record within a partition in the view (e.g. one cell or one bar) and then sum the results for each IF THEN statement before comparing the results between IF THEN statements. For example, Tableau Desktop will sum up all central sales and all west sales within a segment before subtracting west sales from central sales, which means there are two non-NULL numbers to complete the subtraction with.

Option 1 will not work when the dimension(s) used in the condition are added to the view for same reason the original calculation failed: one of the aggregated IF THEN statements will return NULL for each partition in the view. For example, west sales will be NULL for the Central region column.

Options 2 and 3 aggregate the IF THEN statements to a lower level of granularity than shown in the view, which allows for both IF THEN statements to always return a non-NULL number.
Did this article resolve the issue?