4 Replies Latest reply on May 9, 2018 5:48 PM by Shinichiro Murakami

# IF Statement, Cannot mix aggregates and non-aggregates...

I get this error all the time and sometimes understand why and other times I don't. I have the following calc field that I want to apply row-by-row;

IF [Duration] >= 1.67 THEN [Static Data].[Cinema tickets]

ELSEIF [Duration] < 1.67 THEN [Duration]/1.67*[Static Data].[Cinema tickets]

ELSE 0

END

This came up with the error so then I was advised to put "sums" on all the fields. But I don't get why if it's a row-by-row calculation. It works fine in Excel so why not Tableau.

IF SUM([Duration]) >= 1.67 THEN SUM([Static Data].[Cinema tickets])

ELSEIF SUM([Duration]) < 1.67 THEN SUM([Duration])/1.67*SUM([Static Data].[Cinema tickets])

ELSE 0

END

But then I want to apply the formula based on [Person] field:

IF [Person] = "Patient" OR [Person] = "Sibling" OR [Person] = "Friend" OR [Person] = "Cousin" THEN

(IF SUM([Duration]) >= 1.67 THEN SUM([Static Data].[Cinema tickets])

ELSEIF SUM([Duration]) < 1.67 THEN SUM([Duration])/1.67*SUM([Static Data].[Cinema tickets])

ELSE 0

END)

ELSE 0

END

And I get the error again. Can someone please tell why this is wrong and how to fix this?

• ###### 1. Re: IF Statement, Cannot mix aggregates and non-aggregates...

IF MIN([Person]) = "Patient" OR min([Person]) = "Sibling" OR min([Person]) = "Friend" OR min([Person]) = "Cousin" THEN

(IF SUM([Duration]) >= 1.67 THEN SUM([Static Data].[Cinema tickets])

ELSEIF SUM([Duration]) < 1.67 THEN SUM([Duration])/1.67*SUM([Static Data].[Cinema tickets])

ELSE 0

END)

ELSE 0

END

All fields needs be aligned to aggregation, even for single value field.

Thanks.

Shin

1 of 1 people found this helpful
• ###### 2. Re: IF Statement, Cannot mix aggregates and non-aggregates...

Hello, thanks for your response. But can you explain why I need to put MINS - it doesn't make no sense, i'm not trying to find a minimum of anything especially since [Person] is a discrete field

• ###### 3. Re: IF Statement, Cannot mix aggregates and non-aggregates...

Hi Elie,

This is because of the different aggregation levels which was the cause of your issue. The MIN statement is simply converting the logic of the query into an aggregate dataset without actually making any changes to the data that is returned.

HTH

Peter

1 of 1 people found this helpful