9 Replies Latest reply: Sep 12, 2011 4:37 AM by Jason Newman RSS

    Obtain the intersection of variables



      I have an employee database listing (amongst other things) the employee’s grade and skills.

      I wish to be able to select a number of skills and possibly the employee grade to filter down those employees that match my criteria. In other words I want the intersection of my selections.

      I thought it would be best to set up variables for my selections (vSkill1, vSkill2 and vGrade) and have an input box to set these.

      I am struggling to obtain the results of the intersection

      I found the following  aggregation calculation that works with hard codes field values, but not with variables  vSkill1 and vSkill2 :

      =if(aggr(count({1< Skill = {'ACII'} >} [EMPLOYEE.Last Name]),[EMPLOYEE.Last Name])>=1 and aggr(count({1< Skill = {'COGEN'}  >} [EMPLOYEE.Last Name]),[EMPLOYEE.Last Name])>=1,[EMPLOYEE.Last Name])


      And I tried using set analysis I found:


      = $( *=vSkill1()  {vSkill2()}) [Employee.Last Name]




      = {$ < vSkill1()  *= {‘vSkill2()’}>} [Employee.Last Name]



      Neither worked.


      Can someone offer a solution or an alternative method of obtaing the intersection of the selections ?





        • Obtain the intersection of variables
          Goran Korsgren

          Hi Jay


          From your description I do not understand why you want to do it with set analysis at all?


          It sounds like you can do that with just simply selecting in the fields Skill and Grade.



          • Obtain the intersection of variables
            stefano santini

            Hi Jay,

            have you tried with '*' operator in Set Analysis?


            For example something like this:


            sum({<Set analysis>*<Set analysis> } Field)



              • Obtain the intersection of variables



                I have tried using set analysis and *, but unfortunatley I could not get it to work using variables.





                  • Obtain the intersection of variables
                    stefano santini


                    in 1 of mine project i use this:




                    TP_DOC_ESCLUSI is a variable with a lists of value entered manually.


                    It works, so should works something like this:




                    C u

                      • Obtain the intersection of variables


                        I think my ignorance is beginning to show!


                        If I use    = {$< vSkill1={$(#ListSkill1)}>*<vSkill2={$(#ListSkill2)}>}


                        The editor says ‘Expression OK’ , but there is a red line under all of the  statement and vSkill1 and vSkill2 are not in grey (indicating that they are not recognised?, but #ListSKill1 and #ListSkill2 are in grey (indicating they are recognised?).


                        My vSkills variables have values that I have added using an input box.


                        I do not get any return when there is one person that matches the skills I have entered.


                        Whether I have no selections or I select the two skills manually (using a list box) I do not get a result from the equation.


                        Do I need to create variables for the #ListSkills or are they created dynamically, and do I enter values into the #ListSkills rather than vSkills?





                          • Re: Obtain the intersection of variables
                            stefano santini

                            try this jason.

                            '#' is not rilevant in this case, it works with and without '#'.


                            C u,


                              • Re: Obtain the intersection of variables

                                Hi Stefano,


                                Thanks for the file. It is definitely progress but I am still struggling to achieve what I want to.

                                With your solution I would like to be able to select VALUE where the selections in vList1 and vList2 coincide. I tried the following but it did not work:



                                In addition to this I tried using your set analysis with my database and I found I could match a variable with a table value as long as it was numeric.

                                e.g. =SUM({$<EMPLOYEE.Rating={$(#vType1)}>*<EMPLOYEE.Rating={$(#vType1)}>}EMPLOYEE.Rating)

                                where vType1 = 2


                                However, if my input criteria was character based it did not find the corresponding matches.

                                e.g. =SUM({$<Skill={$(#vSkill1)}>*<Skill={$(#vSkill1)}>}Skill)

                                where vSkill1 = ACII


                                I have tried:

                                • -      Putting ‘ ‘ around the input value – i.e. ‘ACII’
                                • -      I tried putting ‘ ‘ around the Skill column name in the equation
                                • -      I tried putting ‘ ‘ around #vSkill1 in the equation
                                • -      I tried using TRIM on the load of Skill

                                None of the above worked. Do you have any suggestions?


                                Many thanks for your help.