4 Replies Latest reply on Jul 26, 2013 4:23 AM by Jim Wahl

Identifying results outside a Standard Deviation

Hi, I'm trying to identify people based on whether their results fall outside of 1 standard deviation of the worksheet average. Ultimately I would like to change their colour based on whether they are within the range or not.

I have tried creating a measure using couple of different formulas including IF(avg([TotalHandleTime]) > WINDOW_STDEV(avg([TotalHandleTime] )) OR (avg([TotalHandleTime]) < WINDOW_STDEV(avg([TotalHandleTime] )))) Then "OUT" Else "IN" END

I Have also tried using just STDEV but it’s not working it keeps showing me everyone is “OUT”

Any suggestions?

• 1. Re: Identifying results outside a Standard Deviation

Maybe the issue is that you need to add / subtract SD from AVG

Here's example from the Coffee data included with Tableau:

Sales <> 1 SD

SUM(Sales) > WINDOW_AVG(SUM(Sales)) + WINDOW_STDEVP(SUM(Sales))

OR

SUM(Sales) < WINDOW_AVG(SUM(Sales)) - WINDOW_STDEVP(SUM(Sales))

This will return a boolean, which you can then drop on the color shelf.

Jim

1 of 1 people found this helpful
• 2. Re: Identifying results outside a Standard Deviation

Perfect worked a treat thank you!

• 3. Re: Re: Identifying results outside a Standard Deviation

Jim and Jessica,

It would seem the same logic could be applied to find the range of +/- a certain number of StdDev over a range such that

Sales <> 2 SD

SUM(Sales) > WINDOW_AVG(SUM(Sales)) + 2*(WINDOW_STDEVP(SUM(Sales)))

OR

SUM(Sales) < WINDOW_AVG(SUM(Sales)) - 2*(WINDOW_STDEVP(SUM(Sales)))

and then a parameter could be utilized in place of "2" to modify this on demand.  Thoughts?

• 4. Re: Re: Identifying results outside a Standard Deviation

Hi Patrick,

Sure, that will work.

Rather than use a boolean, you could also calculate a zscore and color the marks by their zscore (> 2 means the value is more than SD from the mean).

Jim