7 Replies Latest reply: Aug 2, 2012 7:27 PM by Linda Diodato RSS

    AND statement within field

    Linda Diodato

      Hello,

       

      I have a listbox with the following calculated dimensions and I want the list to return only those Partners that match ALL the Specializations that are selected in the C_Specialization field. Is that possible? Any suggestion? Maybe I should use indirect set analysis but I'm not sure how.

       

      Thanks

       

      =if(Specialization=C_Specialization

       

      and

       

      aggr(count(distinct Client)>=1

      , Partner),

       

      Partner)

        • Re: AND statement within field
          Stefan Wühl

          Not sure if I have fully understood, maybe like

           

          =aggr(

          only({<Specialization=C_Specialization, Partner= {"=count(distinct Client)>=1"}> } Partner)

          , Partner)

            • Re: AND statement within field
              Linda Diodato

              Thanks swuehl but I'm afraid with your expression Partner show only when something is selected in C_Specialization, which is not what I'm trying to achieve.

               

              Basically, if I select 2 C_Specializations, I want to see only the Partners where their Specializations (a separate field) matches both selected C_Specializations and not any one of them.

               

              Here is a sample file so that you can see what I mean - check the Nr of Specializations chart: Partners 71634 and 131351 should not show because they only match one of the two selected C_Specializations.

               

              Thanks

                • Re: AND statement within field
                  Stefan Wühl

                  Ok, attached is one possible solution.

                  (Another idea, instead of the data island C_Specialization, you could also consider using a AND_mode list box)

                   

                  I am using this as expression in the listbox:

                   

                  =aggr(

                  only({<Partner= {"=count(distinct Client)>=1 and

                  sum(pow(2,keepchar(C_Specialization,'0123456789'))) =sum(distinct pow(2,keepchar(Specialization,'0123456789')))"}> } Partner)

                  , Partner)

                   

                  I assigned each specialization a binary digit (power of two), so I could compare the two selections just by summing the values (I would rather do the assignment in the script, but I can't reload your sample file and I am too lazy to do a binary load). I've done the assignment just by looking at the numerical in the specialization name, but that's just as workaround for your dummy data, you can do the assignment as you like.

                   

                  If you need to check not on complete equality in selections, but partial (C_Specialization as a subset of Specialization), you could work with binary and operator.

                   

                  Hope this helps,

                  Stefan