4 Replies Latest reply on Jun 2, 2011 9:40 AM by Joe Mako

# IF AND statement, finding the count of an intersection

Hi all,

I'm using this calcuated field with great success to find the number of records sent to a specific location. Its calcuating on the record level and works no matter how I slice and dice it.

count(IF ([LocationName]="A") then [recordID] end)

count(IF ([LocationName]="B") then [recordID] end)

However when I use this to find the intersection

count(IF (([LocationName]="A") AND ([LocationName]="B")) then [recordID] end)

I only get zero values when its definitely not the case.

Can anyone provide any insight?

thanks!

• ###### 1. Re: IF AND statement, finding the count of an intersection

> I only get zero values when its definitely not the case.

It definitely IS the case.  How can you have a record for which  [LocationName]="A" AND [LocationName]="B"?  That is saying count the rows where "A"="B" - which will always be zero.

MAybe you need OR instead of AND?  If not, maybe you can explain what you are trying to count?

• ###### 2. Re: IF AND statement, finding the count of an intersection

A single record can appear multiple times in the table. For example, record 1 has only one row, and has location A. While record 2 has 2 rows, one with location A and one with location B.

• ###### 3. Re: IF AND statement, finding the count of an intersection

You are right. Since LocationName is a single column, its only looking at the single entry and always getting either 4 or 5. I guess my data needs to have a distinct column for each LocationName for this to work?

• ###### 4. Re: IF AND statement, finding the count of an intersection

Depends on your situation, but maybe something like:

```IF FIRST()=0 THEN

WINDOW_SUM(SUM(IIF([LocationName]="A" OR [LocationName]="B",1,0))-1,0,IIF(FIRST()=0,LAST(),0))
END
```

with "recordID" on the Level of Detail shelf, and "recordID" selected as the Compute using for this custom table calculation could work for you (depending on the setup of your worksheet).

Attached is an example of this. Only recordID 2 and 5 have both A and B, so the count returns 2.

If you want to add a third location:

```IF FIRST()=0 THEN

WINDOW_SUM(SUM(IIF([LocationName]="A" OR [LocationName]="B" OR [LocationName]="C",1,0))-2,0,IIF(FIRST()=0,LAST(),0))
END
```

notice that the -1 changed to a -2 in addition to an additional OR conditional statement.

If this does not work for your situation, can you provide a sample packaged workbook that represents your situation?