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

    Obtain the intersection of variables

      Hi,

       

      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]

       

      Or

       

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

       

       

      Neither worked.

       

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

       

      Regards

       

      Jason

        • 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.

           

          /gg

          • 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)

             

            Stefano.

              • Obtain the intersection of variables

                Stefano,

                 

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

                 

                Regards

                 

                Jason

                  • Obtain the intersection of variables
                    stefano santini

                    Jason,

                    in 1 of mine project i use this:

                     

                    {$<TP_DOC_BS={$(#TP_DOC_ESCLUSI)}>}

                     

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

                     

                    It works, so should works something like this:

                     

                    {$<vSkill1={$(#ListSkill1)}>*<vSkill2={$(#ListSkill2)}>....}

                     

                    C u

                      • Obtain the intersection of variables

                        Stefano,

                        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?

                         

                        Regards

                         

                        Jason

                          • Re: Obtain the intersection of variables
                            stefano santini

                            try this jason.

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

                             

                            C u,

                            Stefano.

                              • 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:

                                =SUM({$<{$(#vLIST1)={$(#vLIST2)}>*<{$(#vLIST1)={$(#vLIST2)}>}VALUE)

                                 

                                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.

                                Jason