How to perform an 'AND' with set analysis? - Qlik Community

Report Inappropriate Content · ‎2010-03-08

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.

Report Inappropriate Content · ‎2010-03-08

it is perhaps easier to do this as:

sum (

if(

(

(Familiy=1 or Family=2)

AND

(Department=K or Department=F)

)

,

Amount)

jedgson · ‎2010-03-08

Try


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

Report Inappropriate Content · ‎2010-03-08

<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( {<Department =-{'B','C'},Family=-{'1','2'} >}Qty) worked as an AND. </div> <div>However, when Depart & 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>

ovcharenko · ‎2011-08-19

Hi, dragonauta!

In this case it is needed to use an union

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

good luck!

Sergey.