Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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.

Tags (1)
4 Replies
Not applicable

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)

jedgson
Contributor

How to perform an 'AND' with set analysis?

Try


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


Not applicable

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>

ovcharenko
New Contributor II

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.

Community Browser