6 Replies Latest reply on Mar 26, 2018 5:45 AM by David Maning

# cannot mix aggregate and non-aggregate arguments with this function

Hi guys,

I have a little challenge to get the formula below working: Basically I would like to check whether conditions of material status [MS] are met and if so Tableau should return calculations shown below.

However this does not work as I obviously mix aggregated and non-aggregated arguments (see subject header)

I am wondering how I can overcome this issue. The check of material status [MS] is mandatory before returning values as otherwise the outcome will be not correct.

Is there any simple solution to solve those issues?

Thank you in advance

Br

seb

IF [MS]<>"BI" and [MS] <> "BO"

then [Correct Stock AS]/([Total GI QTY]/-5)

ELSEIF [MS] = "BO"

then ([Correct Stock AS]/([Total GI QTY -3 years]*-1))

END

• ###### 1. Re: cannot mix aggregate and non-aggregate arguments with this function

Hi Sebastian,

Just  use attribute. example:

IF ATTR([State]) <> 'Alabama'

THEN SUM([Profit])/SUM([Quantity])

END

Trust this helps.

D

1 of 1 people found this helpful
• ###### 2. Re: cannot mix aggregate and non-aggregate arguments with this function

Good morning -

What tableau is telling you is that you created an aggregation in one of the values you put in the formula and you will need to aggregate the other variables to make the calculation valid

Aggregation functions are not limited to sum() but include Min(),Max, count(), countd(), avg() , attr() and others

Not knowing where the aggregate is in your function I would suggest

IF Attr([MS])<>"BI" and attr([MS]) <> "BO"

then ( sum([Correct Stock AS])/([Total GI QTY]/-5)

ELSEIF attr( [MS]) = "BO"

then (sum([Correct Stock AS])/([Total GI QTY -3 years]*-1))

END

but that is assuming that [Total GI QTY and Total GI QTY -3 years] are the aggregates

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: cannot mix aggregate and non-aggregate arguments with this function

Sebastian,

Because you did not attach workbook, so I don't know if these measures: Correct Stock AS,  Total GI QTY, Total GI QTY -3 years, are aggregated or non-aggregated. If they are aggregated, then you need to add ATTR before dimension MS.

Hope it helps.

Michael Ye

• ###### 4. Re: cannot mix aggregate and non-aggregate arguments with this function

Hi,

I really appreciate your input and your support. It solved my challenge.

Many thanks

Have a great day

Br

Sebastian

• ###### 5. Re: cannot mix aggregate and non-aggregate arguments with this function

Thanks

if this solved your issue please mark my response correct to close the thread

Jim

• ###### 6. Re: cannot mix aggregate and non-aggregate arguments with this function

You are welcome!

Please mark my response as correct and helpful.

Thanks.

D