8 Replies Latest reply on Apr 26, 2018 9:11 AM by Jim Dehner

# Calculating outliers using standard deviation

I am trying to create a dimension to use as a filter to filter out data that lies above 3 standard deviations using a table calculation that looks like this:

IF [Total Concentration ] > 3*[Standard Deviation ] THEN 'Outlier'

ELSE 'Non-Outlier'

END

"Total Concentration" is a table calc that looks like: {Fixed [ID #] : AVG([Measure 1]*4) +AVG( [Measure2]*10) + AVG([Measure3]*1) + AVG([Measure4]*10)}

"Standard Deviation" is simply "STDEVP[Total Concentration]"

I am getting the error message because I cannot mix aggregate and non-aggregate arguments, but I feel like I'm pretty close. Can anyone help?

Thanks!

• ###### 1. Re: Calculating outliers using standard deviation

HI James

Could you please attach sample data as packaged workbook(**.twbx).

Thanks,

Shin

• ###### 2. Re: Calculating outliers using standard deviation

Hi James - total concentration looks like an LOD not a table calculation - it is not aggregated so your conditional statement is looking for an aggregation

Try this

IF    SUM(      [Total Concentration ]    )   > 3*[Standard Deviation ] THEN 'Outlier'

ELSE 'Non-Outlier'

END

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: Calculating outliers using standard deviation

sorry that aggregation should have been min() and not sum

IF    MIN(      [Total Concentration ]    )   > 3*[Standard Deviation ] THEN 'Outlier'

ELSE 'Non-Outlier'

END

Jim

• ###### 4. Re: Calculating outliers using standard deviation

I've attached a sample workbook

• ###### 5. Re: Calculating outliers using standard deviation

Hi Jim, adding Min() does make the calculation valid but does not achieve my objective unfortunately. I have attached a sample workbook to my post. Thanks!

• ###### 6. Re: Calculating outliers using standard deviation

see the attached

I changed the std dev calculation to

that makes the outlier calculation correct

and will return this

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.

• ###### 7. Re: Calculating outliers using standard deviation

Jim,

That's what I was missing, thank you!!