2 Replies Latest reply on Aug 26, 2015 10:01 PM by Alex Martell

# Dynamic threshold in if statement

I want a formula that does this:

groupVarietalValid:

IF {FIXED[Varietal] : SUM([Quantity]) } > 34 THEN [Varietal] ELSE "Other" END

That works, but instead of 34 being a fixed number, I want it to be a dynamic number based on the total quantity for the table.

So something like this:

groupVarietalNotValid:

IF {FIXED[Varietal] : SUM([Quantity]) } > TOTAL(SUM([Quantity])) * 21 / 144 THEN [Varietal] ELSE "Other" END

When I do this (and countless other variations trying to get it to work), it gives me the "cannot mix aggregate etc." error

Any help hugely appreciated.  I have attached my data and calculations.

• ###### 1. Re: Dynamic threshold in if statement

You can write it like this

if sum([Quantity]) > TOTAL(SUM([Quantity])) * 21 / 144 then ATTR([Varietal]) else "Other" END

Given varietal is already a field in the visualization.

• ###### 2. Re: Dynamic threshold in if statement

That did it.  Thanks!

Sent from Mailbox

On Thu, Aug 27, 2015 at 2:57 PM, Bora Beran