Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
it is perhaps easier to do this as:
sum (
if(
(
(Familiy=1 or Family=2)
AND
(Department=K or Department=F)
)
,
Amount)
Try
sum({<Family-={'1','2'}> * <Department-={'K','F'}>} Amount)
<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>
Hi, dragonauta!
In this case it is needed to use an union
sum({<Family-={'1','2'}> + <Department-={'K','F'}>} Amount)
good luck!
Sergey.
