1 2 Previous Next 19 Replies Latest reply on Feb 18, 2016 4:57 PM by Bora Beran

# Nested IF statement using And/Or

 x1 99.996 x2 99.995 x3 99.997 x4 99.999
```if
(([Hostname]='x1' or [Hostname]='x2') and [% Uptime (External)]>=99.9)
and
(([Hostname]='x3' or [Hostname]='x4') and [% Uptime (External)]>=99.9)
then 1
ELSE 0 END
```

What I'm attempting to do is compare x1 and x2 together, and make sure they are above 99.9. Then compare x3 and x4 together and make sure they are above 99.9. In tableau, this equation generates a 0. Why? Note that all of the values are above 99.9 anyway, so its very confusing. Can you help?

• ###### 1. Re: Nested IF statement using And/Or

For any given row you don't have both conditions satisfied.

e.g for the first row the value is x1 but not x3 or x4. Since you have AND between those comparisons and second line hostname=x3 or hostname=x4 returns false for 1st row, it makes the entire if statement false.

If you make it

`if (([Hostname]='x1' or [Hostname]='x2') and [% Uptime (External)]>=99.9) OR (([Hostname]='x3' or [Hostname]='x4') and [% Uptime (External)]>=99.9) then 1 ELSE 0 END `

it will probably give you what you're looking for if I understand your goal correctly.

• ###### 2. Re: Nested IF statement using And/Or

Hi Christopher,

You've written a Row-Level Calculation.

As a name implies it is calculated for each row in a dataset.

And the result is a value per row.

You wrote above:

<snip>
Then compare x3 and x4 ...
</snip>

Namely, it is 'Then' translated into 'AND' in your calc creates a logical fallacy.

Tableau can not find any single row for which both parts of your statement are TRUE.

To explain I prepare a workbook with a four-row dataset and a boolean logic of yours.

Hope it helps.

Yours,

Yuri

• ###### 3. Re: Nested IF statement using And/Or

Thank you Bora, you've given an answer while I've been making a wb.

• ###### 4. Re: Nested IF statement using And/Or

Bora,

Using the OR function is slightly different from what I'm looking for

if I change x3 and x4 to 99.8 each (failing), and I run your function, it would return 1 (true) because of x1 and x2

I need either x1 and x2 to be 99.9 and I need either x3 and x4 to be 99.9 for the entire statement to be 1

• ###### 5. Re: Nested IF statement using And/Or

Then you need to break into pieces e.g.

`if (([Hostname]='x1' or [Hostname]='x2') and [% Uptime (External)]>=99.9) THEN 1ELSEIF (([Hostname]='x3' or [Hostname]='x4') and [% Uptime (External)]>=99.9) then 1 ELSE 0 END `
1 of 1 people found this helpful
• ###### 6. Re: Nested IF statement using And/Or

The following link has an example of how to use the And/Or operatoers in conditional statements.

• ###### 7. Re: Nested IF statement using And/Or

Hey Bora,

First, thank you for helping me. This is really driving me insane!

I've attached a workbook, and I used the OR statement and it's not getting what I want. As you can see it is generating a 1, or a pass. Let me reiterate, I need x1 or x2 to be 99.9 AND x3 or x4 to be 99.9, and if either of those fail, then the whole thing should fail.

To give you perspective, x1-x4 are all servers. x1/x2 are in one location, x3/x4 in another. I'm taking the highest value for x1/x2 and x3/x4, comparing them and if the lowest value (of the two highest values) is less than 99.9 the whole thing fails.  That translates to "Customer 1" failing to meet standards. The hard part about this is having a calculation go through more than 1 row.

• ###### 8. Re: Nested IF statement using And/Or

If you change the field called 'Aggregate' to have this formula instead

WINDOW_MAX(if SUM([Or])>=1 then 1 else 0 end)

it should give you what you want. E.g. if you want any of the rows having 1 making Customer1 to show 1, you should click on the pill select Edit Table Calculation, then from Compute Using dropdown select Advanced... and put Customer into Partition box.

If everything is 0 for the customer this would give you zero. If there is 1 or more 1s then it will give you 1.

• ###### 9. Re: Nested IF statement using And/Or

Even though your calculations are valid, they are not producing the correct results. With the data as is, it's showing as a pass, where it should be a fail.

Maybe Tableau doesn't have the functionality? Or if you can solve it, can you attach your workbook?

• ###### 10. Re: Nested IF statement using And/Or

Would something like this work for you:

IF COUNTD([Hostname]) <> SUM({FIXED [Hostname]:SUM(IF [Uptime]>=99.99 THEN 1 ELSE 0 END)})

THEN 'Fail' ELSE 'Pass' END

• ###### 11. Re: Nested IF statement using And/Or

Following your earlier suggestion i.e. get the max of each pair then get the overall min and compare with 99.99

IF {fixed [Realm] : MIN({fixed [POP], [Realm] : MAX([Uptime])})} > 99.99 THEN 1 ELSE 0 END

Here

{fixed [POP], [Realm] : MAX([Uptime])}  gives you the max of x1/x2 and x3/x4

then the calc surrounding it is taking the min of the first step for each customer.

• ###### 12. Re: Nested IF statement using And/Or

I added a second customer to the data set to test it out...

Slight modification to calc:

IF

WINDOW_SUM(SUM(IF [Hostname]='x1' AND {FIXED [Realm],[Hostname]:SUM([Uptime])}>=99.9 THEN 1 ELSE 0 END))+

(SUM(IF [Hostname]='x2' AND {FIXED [Realm],[Hostname]:SUM([Uptime])}>=99.9 THEN 1 ELSE 0 END))>=1

AND

WINDOW_SUM(SUM(IF [Hostname]='x3' AND {FIXED [Realm],[Hostname]:SUM([Uptime])}>=99.9 THEN 1 ELSE 0 END))+

(SUM(IF [Hostname]='x4' AND {FIXED [Realm],[Hostname]:SUM([Uptime])}>=99.9 THEN 1 ELSE 0 END))>=1

THEN 'Pass' ELSE 'Fail' END

Attaching 9.1 workbook

1 of 1 people found this helpful
• ###### 13. Re: Nested IF statement using And/Or

Hi all,

A slight variation on a theme (INCLUDE == Measure, could be FIXED == Dimension, of course):

Yours,

Yuri

• ###### 14. Re: Nested IF statement using And/Or

This is the closest to my end goal. I need time to dissect it, but the only problem is this needs to be scaled up to multiple customers with different hostnames. So for Example, for Customer 2 it would be y1, y2, y3, y4. I tried copying the window sum function, and using it in an ELSEIF, but no go.

Any ideas?

1 2 Previous Next