10 Replies Latest reply on Sep 13, 2018 10:44 AM by Hari Ankem

# How to Create a Calculation with IF and OR and AND statements

Hi,

I am attempting to create a calculation with IF and OR statements, however, my calculations contain errors and I'm unsure on what to fix. I am trying to create the following statements using testing percentile scores and have attempted to create number 1:

IF AVG([M. Attainment1]) OR AVG([R. Attainment1])>90 THEN "R or M Topline"END

ELSEIF  AVG([M. Attainment1])OR AVG([R. Attainment1])<90 AND >50 THEN

"Below R or M Topline"

END

1. 90%ile or above in ONE of reading & math (other >= 50) Worksheet 1

2. 90%ile or above in ONE of reading & math (other >= 70) Worksheet 2

3. 70%ile or above in ONE of reading & math (other >= 50) Worksheet 3

Rashida

• ###### 1. Re: How to Create a Calculation with IF and OR and AND statements

It would help a lot to see your real data - for instance, is the number really 90 or is it .9?

You have too many ENDs in the calc: you only need one, as the last line.  And your comparators need to be fully stated.

Making some possibly incorrect assumptions, this should be close.  I changed the logic slightly - this may not be exactly what you want but you were excluding cases where the Attainment number is exactly 0.9.

IF AVG([M. Attainment1]) >= 0.9

OR AVG([R. Attainment1]) >= 0.9

THEN 'R or M Topline'

ELSEIF (AVG([M. Attainment1]) >= 0.5

AND AVG([M. Attainment1]) < 0.9)

OR (AVG([R. Attainment1]) >= 0.5

AND AVG([R. Attainment1]) < 0.9

THEN 'Below R or M Topline'

END

1 of 1 people found this helpful
• ###### 2. Re: How to Create a Calculation with IF and OR and AND statements

I am not completely clear on your business logic. But, you need to write like this:

IF AVG([Attainment 1]) >90 OR AVG([Attainment 2])>90

THEN

"R or M Topline"

ELSEIF  (AVG([Attainment 1])<90 AND AVG([Attainment 2])>50)

OR (AVG([Attainment 1])<50 AND AVG([Attainment 2])>90)

THEN

"Below R or M Topline"

END

1 of 1 people found this helpful
• ###### 3. Re: How to Create a Calculation with IF and OR and AND statements

Hari, you were in my head!

1 of 1 people found this helpful
• ###### 4. Re: How to Create a Calculation with IF and OR and AND statements

Thanks, Michael! I am pretty new to Tableau and am having difficulty with this syntax.

I used the following:

IF AVG([M. Attainment1]) >=90

OR AVG([R. Attainment1]) >=90

THEN "R or M Topline"

ELSEIF (AVG([M. Attainment1])>=50

AND AVG([M. Attainment1])<90)

OR (AVG([R. Attainment1])>=50

AND AVG([R. Attainment1])<90)

THEN "Below R or M Topline"

END

Thanks!

• ###### 5. Re: How to Create a Calculation with IF and OR and AND statements

Yes,

Michael provided me with the same answer. If possible, can you all share what you find to be the most helpful resources and videos around this subject and creating calculations?

Thanks,

Rashida

• ###### 6. Re: How to Create a Calculation with IF and OR and AND statements

I didn't understand what you mean Michael.

• ###### 8. Re: How to Create a Calculation with IF and OR and AND statements

Sorry if the logic seemed confusing. I am trying to show which schools' average Attainment score was 90% or more in Math or Reading while the corresponding subject (Math or Reading) is 50% or more. For example, John Does HS scored 93% Attainment in Math but 54% Attainment in Reading and Jane Doe Middle School has the opposite where they scored 90%  Attainment in Reading and 85% in Math.

Thanks,

Rashida

• ###### 9. Re: How to Create a Calculation with IF and OR and AND statements

Hari, it's a colloquial expression that means "we're thinking the same thing at the same time", more or less.

Or in other words "stop reading my mind"!

• ###### 10. Re: How to Create a Calculation with IF and OR and AND statements

Hmmm...actually it was the reverse since you wrote the detailed solution before I did. Anyway, have fun.