KNOWLEDGE BASE

Error: "Cannot mix aggregate and non-aggregate arguments with this function" When Creating a Calculated Field


Published: 11 Jan 2016
Last Modified Date: 24 Jul 2017

Issue

When creating a calculation, the following error might occur: 

"Cannot mix aggregate and non-aggregate arguments with this function."

Environment

Tableau Desktop

Resolution

Modify the calculation so that all fields are either aggregate or non-aggregate. However, based on the specific calculation you are using, the best solution may differ.

Option 1

Wrap all fields in an aggregation. For example, the calculation: [Sales] + SUM( [Profit] ) could become: SUM( [Sales] ) + SUM( [Profit] )

Option 2

Remove aggregations from all of the fields. For example, the calculation: [Sales] + SUM( [Profit] ) could become [Sales] + [Profit]

Option 3

Move the aggregation so all fields are aggregated. 

For example, the calculation:
IF YEAR( [Date] ) = 2017
THEN COUNTD( [ID] )
END 
could become:
COUNTD(
IF YEAR( [Date] ) = 2017
THEN [ID]
END )

Option 4

Use the FIXED Level of Detail Expression to make an aggregation non-aggregate. For example, the calculation: [Date] = MAX( [Date] ) could become: [Date] = { FIXED : MAX( [Date] ) }

Cause

Aggregations are computed at the level of detail in the view,  which will return one value for several records. Non-aggregate fields are computed for every record in the underlying data, which will return one value per record.

Tableau Desktop would not know how to compare the one value of the aggregation to the several values of the non-aggregated field.
Did this article resolve the issue?