KNOWLEDGE BASE

Aggregating Calculations With IF THEN Statements Has NULL Result


Published: 10 Dec 2015
Last Modified Date: 07 Sep 2016

Issue

When aggregating two calculations, or finding the difference between two calculations, that include IF THEN statements, the results might be NULL. For example, the following example using Superstore data will always return NULL: 
SUM( IF [Region] = "Central" THEN [Sales] END ) - SUM( IF [Region] = "West" THEN [Sales] END )

Environment

Tableau Desktop

Resolution

Option 1 

Use Level of Detail (LOD) expressions. For example:
{ FIXED : SUM( IF [Region] = "Central" THEN [Sales] END ) } - { FIXED : SUM( IF [Region] = "West" THEN [Sales] END ) }

Option 2

Wrap the calculations in WINDOW_SUM() functions. For example:
WINDOW_SUM( SUM( IF [Region] = "Central" THEN [Sales] END )) - WINDOW_SUM( SUM( IF [Region] = "West" THEN [Sales] END ))

Cause

When calculations with IF THEN statements are run, each record in the database is read to determine if the IF conditional is true before returning the measure values. Results are only available where the IF conditional is true.
Did this article resolve the issue?