
1. Re: Calculate a Conditional Average
Brad Llewellyn Mar 18, 2013 7:01 AM (in response to david.berrie)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,
Brad Llewellyn
Associate Consultant
Mariner, LLC
brad.llewellyn@marinerusa.com

2. Re: Calculate a Conditional Average
david.berrie Mar 18, 2013 7:07 AM (in response to Brad Llewellyn)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
Brad Llewellyn Mar 18, 2013 7:12 AM (in response to david.berrie)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,
Brad Llewellyn
Associate Consultant
Mariner, LLC
brad.llewellyn@marinerusa.com

4. Re: Calculate a Conditional Average
david.berrie Mar 18, 2013 8:28 AM (in response to Brad Llewellyn)Hi Brad,
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
Brad Llewellyn Mar 18, 2013 8:33 AM (in response to david.berrie)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,
Brad Llewellyn
Associate Consultant
Mariner, LLC
brad.llewellyn@marinerusa.com

6. Re: Calculate a Conditional Average
david.berrie Mar 18, 2013 10:16 AM (in response to Brad Llewellyn)Hi Brad,
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