<< Back

Fixing Tableau Errors: Cannot mix Aggregate and Non Aggregate Arguments

If you’ve arrived at this blog it’s likely you’re experiencing this error.

In short, the reason for this error is that Tableau cannot mix an aggregate argument e.g. sum(Sales), avg(Profit), Profit Ratio with non-aggregate arguments e.g. Discount, Sub-Category. In order to fix it you need to make all arguments aggregates or all arguments non-aggregates / row level variables.

If you have time then we recommend this video where Zen Master Chris Love explains the terms and shows three examples of how to fix the error. If you’re in a rush, or prefer to read the solution, then look below the video where this post continues.

 

What are Aggregate / Non-Aggregate Variables?

Aggregate variables are values that are calculated in the view, and depend on the level of aggregation in Tableau. e.g. sum(Sales) will show different values in Tableau if it’s next to a Region dimension, or if it’s next to a Category dimension. Aggregates are identified in Tableau by the prefix AGG when using them in Tableau. Note that Tableau Calculations  in Tableau are also aggregate functions.

Non-aggregate variables and at the “row level” in the data and normally need to be aggregated by Tableau – so you’ll see their pills with an aggregation wrapped around them in sum( ) or avg( ) in the view pane.

How do you fix the error?

Generally there are two ways. e.g. for the formula below:

IF Sum(Sales)>50000 then ([Region] else ‘None’ end

we can fix it by making the Region an ATTR (an attribute)

IF Sum(Sales)>50000 then ATTR([Region]) else 'None' end

However we need to be careful that Region is in our or is at least unique when this calculation is aggregated by the view, if it isn’t then the calculation won’t work. See the video for why!

Another, more advanced, solution we show in the video is to use a FIXED calculation to move the aggregation down to the ow level, e.g.:

IF {FIXED [Sub-Category]Sum(Sales)}>50000 then [Region] else ‘None’ end

Again though you need to be careful, so watch the video!

 

 

Chris Love

Nottingham, UK

One thought on “Fixing Tableau Errors: Cannot mix Aggregate and Non Aggregate Arguments

Leave a Reply

Your email address will not be published. Required fields are marked *