2 Replies Latest reply on Jul 27, 2016 12:55 PM by Tae-Jin Kim

# compare non-aggregate to aggregate

Let's say that I have a field of decimal values called Val.

How can I create a new field that categorizes any values greater than the standard deviation of Val into "1" and rest into "2"?

I tried:

IF [Val] > STDEV([Val]) THEN "1"

ElSE "2" END.

But I get an error "cannot mix aggregate and non-aggregate arguments with this function."

How can I get around this?

• ###### 1. Re: compare non-aggregate to aggregate

Hi Tae,

when you write [Val] it works at Row level and when you write Sum([Val]) it works as a aggregation. If you notice STDDEV is the aggregated function which require series of data and which can't be calculate over single value (cell).

Thus as per your formula [val] is working at row level and STDEV([Val]) is working at the aggregated level which should not be the case. Comparisons are done at the either at row level or aggregated level. Your formula is mixing two things. Thus this formula thrown an erroe of "cannot mix aggregate and non-aggregate arguments with this function."

Try doing

IF Sum([Val]) > STDEV([Val]) THEN "1"

Else "2" END.

I hope now you are clear with this stuff.

Thanks and Regards,

-Ashish Chaudhari

• ###### 2. Re: compare non-aggregate to aggregate

Hi,

May I ask why you would compare Sum([Val]) to the STDEV([Val])?

I actually am trying to compare individual values in Val to the standard deviation.

For example, if I have values -1, 0, 1, 2 and a stdev of 0.5, then I want to -1, 0  to be "2" and 1, 2 to be "1".

I tried what you have written above and actually all of the values are categorized to "2".

TJ