6 Replies Latest reply on Mar 18, 2013 10:16 AM by david.berrie

# Calculate a Conditional Average

I want to be able to calculate an average based on the underlying data that makes up the calculation.  In simple terms, I need to exclude any value that is considered an 'outlier'.

I'm fairly new to tableau, and cannot seem to get the calculation logic to work correctly.

I'm using a parameter so that I can allow someone to either include or exclude the outlier values.

Here's what I want to do:

IF [Outlier_Parameter] = 'Include'

THEN  AVG([number_of_days])

ELSE  AVG([number_of_days]) WHERE [number_of days] >2 and [number_of_days] <40

END

Can anyone help with my syntax here?

Thanks

• ###### 1. Re: Calculate a Conditional Average

David,

Thanks for posting on the forums.  I hope your experience with Tableau has been positive.  In words, what do you want your calculation to be?  I can see a few issues with your posted code, but it would be improper of me to speculate about your intended use for it.

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 2. Re: Calculate a Conditional Average

Basically, If my parameter is set to exclude the outliers, then I only want to calculate the average when the value of [number_of_days] is between 3 and 39.

So I need to  exclude all of the records where [number_of_days] is 0,1,2 and > 40.

Is that clear?

• ###### 3. Re: Calculate a Conditional Average

David,

That seems quite sufficient.  Try the following code:

IF [Outlier_Parameter] = "Include"

THEN  AVG([number_of_days])

ELSE

AVG(

IF [number_of days] > 2 AND [number_of_days] < 40 THEN [number_of_days] END

)

END

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 4. Re: Calculate a Conditional Average

Thanks for the suggestion.  I can get the calculation to work for the Include part, but the part that employs this code:

AVG (

IF [number_of_days]>2 AND [number_of_days]<40 THEN [number_of_days] END

)

does not seem to work.  I get a single line in my viz that has the #QNAN next to it.  I'm guessing that this is some sort of a 'Not a Number' error.  I'm still looking into it.

Thanks so much for your help.

D

• ###### 5. Re: Calculate a Conditional Average

David,

If there are no values inside the AVG() function, it may return NAN.  You could add another if statement to make this NULL if there are no values.

Thanks,

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 6. Re: Calculate a Conditional Average

Thanks so much for your help.  I got the calculation to work using this statement:

AVG (

ZN(IF [number_of_days]>1 AND [number_of_days]<40 THEN [number_of_days] END)

)

Thanks again!

D