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

    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?
              Ben Laubach

              <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?
                Sergey Ovcharenko

                Hi, dragonauta!

                 

                In this case it is needed to use an union

                 

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

                 

                good luck!

                Sergey.