Aggregating Calculations With IF THEN Statements Has NULL Result

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


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 )


Tableau Desktop


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


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?