KNOWLEDGE BASE

IF Statement With Aggregated Fields Returns Either Nothing or Everything.


Published: 06 Feb 2017
Last Modified Date: 08 Feb 2017

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 nothing, or everything.

 

Environment

Tableau Desktop

Resolution

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 )

Cause

When [Region] is not in the view, then every partition will return one value of [Region]. If the returned minimum value of [Region] matches the condition then all values for all regions will be returned, otherwise no values will be returned.

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.
Did this article resolve the issue?