4 Replies Latest reply: Aug 19, 2011 3:59 AM by Sergey Ovcharenko

# How to perform an 'AND' with set analysis?

Hi,

I'm trying to exclude certain records with set analysis using the following statement :

`sum({<Family-={'1','2'},Department-={'K','F'}>} Amount)`

But this statement is executing as an 'OR' and I want to exclude records where only both conditions are met. How should I go about it?

Thanks.

• ###### How to perform an 'AND' with set analysis?

it is perhaps easier to do this as:

sum (

if(

(

(Familiy=1 or Family=2)

AND

(Department=K or Department=F)

)

,

Amount)

• ###### How to perform an 'AND' with set analysis?

Try

`sum({<Family-={'1','2'}> * <Department-={'K','F'}>} Amount)`

• ###### How to perform an 'AND' with set analysis?

<body><p> <p>LOAD * INLINE [<br />    Family, Department, Qty<br />    1 , A, 1<br />    2 , A, 1<br />    3 , A, 1<br />    4 , A, 1<br />    1 , B, 1<br />    2 , B, 1<br />    3 , B, 1<br />    4 , B, 1<br />    1 , C, 1<br />    2 , C, 1<br />    3 , C, 1<br />    4 , C, 1<br />    1 , D, 1<br />    2 , D, 1<br />    3 , D, 1<br />    4 , D, 1    ];</p> <div>The expression </div> <div>Sum( {&lt;Department =-{&#39;B&#39;,&#39;C&#39;},Family=-{&#39;1&#39;,&#39;2&#39;} &gt;}Qty) worked as an AND. </div> <div>However,  when Depart &amp; Family where in different tables it was not working the same way.  It seemed to ignore the set on the fields that were not in the same table as QTY. </div> <div></div> <div><col width="76"></col> <col width="44"></col> <col width="98"></col> <tr> <td width="76" class="xl24" height="17">Department</td> <td width="44" class="xl24">Family</td> <td width="98" class="xl24">Sum</td> </tr> <tr> <td class="xl25" height="17"> </td> <td class="xl25"> </td> <td align="right" class="xl25">4</td> </tr> <tr> <td class="xl26" height="17">A</td> <td align="right" class="xl26">3</td> <td align="right" class="xl26">1</td> </tr> <tr> <td class="xl26" height="17">A</td> <td align="right" class="xl26">4</td> <td align="right" class="xl26">1</td> </tr> <tr> <td class="xl26" height="17">D</td> <td align="right" class="xl26">3</td> <td align="right" class="xl26">1</td> </tr> <tr> <td class="xl27" height="17">D</td> <td align="right" class="xl27">4</td> <td align="right" class="xl27"> <p>1</p> <p> </p> </td> </tr> </div> <div><col width="98"></col><col width="98"></col><col width="98"></col></div> </p></body>

• ###### How to perform an 'AND' with set analysis?

Hi, dragonauta!

In this case it is needed to use an union

sum({<Family-={'1','2'}> + <Department-={'K','F'}>} Amount)

good luck!

Sergey.