KNOWLEDGE BASE

IF Statement With Aggregated Fields Returns Either Nothing or Everything


Published: 06 Feb 2017
Last Modified Date: 20 May 2020

Issue

When using a calculation with an IF statement which contains aggregated fields and an aggregated field used in the calculation is not present in the view, the calculation returns unexpected results - either NULL or everything.

 

Environment

Tableau Desktop

Resolution

Option 1

Move the IF statement inside the aggregation. For example: 
  • The following calculation: IF MIN( [Region] ) = "Central" THEN SUM( [Sales] ) END
  • Would become: SUM( IF [Region] = "Central" THEN [Sales] END )

Option 2

Add the dimension from the condition to the view. For example:
  • In the following calculation: IF MIN( [Region] ) = "Central" THEN SUM( [Sales] ) END
  • The only conditional statement (aka statement that returns either true or false) is MIN( [Region] ) = "Central". Therefore we would want to add [Region] to the view.

Cause

MIN( [Region] ) is calculated for every partition (e.g. bar, circle, cell, etc...) in the view. When [Region] is not in the view, then every partition may have multiple values of [Region], but MIN( [Region] ) will only return the minimum of all of the values of [Region] in that partition.

For example, if a particular customer does business in multiple regions, but the first region in the list is "Central" then MIN( [Region] ) = "Central" will be true for all transactions from that customer, and all sales will be returned.


Notes

Did this article resolve the issue?