13 Replies Latest reply on Oct 23, 2014 4:35 AM by Nora Kelmendi Branched to a new discussion.

'And' functions in 'If' statements

Hello,

I am trying to write an 'If' formula using an 'And' function but it does not seem to be working:

IIf([Level]="3" AND [Overall Pass Rate]<=0.6,"1",0)

Is this possible?

Nora Kelmendi

• 1. Re: 'And' functions in 'If' statements

This is possible it could possibly by a syntax error. I have done an example with the superstore data. If you post a packed workbook I will gladly take a look at it for you • 2. Re: 'And' functions in 'If' statements

Thank you for your response.

I am unfortunately unable to post a packaged workbook as it contains confidential data.

I have followed your example and I am getting the error message "Cannot mix aggregate and non aggregate arguments with this function".

• 3. Re: 'And' functions in 'If' statements

You can post a packaged workbook even if you have confidential data if you do what I suggested in another thread.  See here:  Re: combine two worksheets into a single viz

As it stands now, you're going to get answers based on guesses.  For instance, I'm guessing that the calc field [Overall Pass Rate] is some sort of an aggregation, but [Level (copy)] is not.  If that's true, use ATTR([Level (copy)]) instead.

Providing a sample packaged workbook let's us walk through it together.

• 4. Re: 'And' functions in 'If' statements

By putting " " around the 1 you are telling Tableau to return a string, but then the 0 is a number. You can't mix outputs like this. Same thing is true of the " " around the 3 and the 0.6. You can't mix strings and numbers. Get rid of the quotes or put quotes around everything.

--Shawn

• 5. Re: 'And' functions in 'If' statements

That was going to be my nest guess for this

• 6. Re: 'And' functions in 'If' statements

You have Nora on the right track.  It's just syntax at this point, specific to her workbook.

• 7. Re: 'And' functions in 'If' statements

Shawn -- Overall Pass Rate is a numeric field, but we can't see what data type [Level} is.  If it's a char field, the quotes are right.

She would have gotten a different error message if she put quotes around a numeric value.

• 8. Re: 'And' functions in 'If' statements

When I posted I didn't see a second post from her so I didn't see the screenshot. Oh well I'll leave you guys to it.

--Shawn

• 9. Re: 'And' functions in 'If' statements

I have created an example packaged workbook but I'm not sure how to upload it as I have never done it before.

• 10. Re: 'And' functions in 'If' statements

You need to click use advanced editor then click attach next to mention

• 11. Re: 'And' functions in 'If' statements

Its been a looong day here!

What I am trying to calculate:

if (level) = 3 and overall pass rate <= 60% then count the number of modules,

if (level) =4 and overall pass rate <= 75% then count the number of modules.

• 12. Re: 'And' functions in 'If' statements

Going off of what Joe Oppelt mentioned earlier I created the calculation with ATTR([Level]) and the calculation was valid

IIF(ATTR([Level])= '3' AND [Overall Pass Rate] <=.6, 1, 0)

IIF(ATTR([Level])= '4' AND [Overall Pass Rate] <=.75, 1, 0)

Have you tried this to see if it works like you expect?

• 13. Re: 'And' functions in 'If' statements

Thank you, this works.

I am now unable to Sum this function.