Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi GG,
I can get an intersection of results for two different fields but if I select two Skills I get the union and not the intersection (i.e. 10 people have one skill, 5 people have another skill and only one person has both - I want the one person highlighted, but the results I get return 14 people highlighted)
Regards
Jason
Yes, on second thoughts I suspected that was what you wanted.
You can do it if you use an "and mode" field.
See 12.6 in the QV documentation
/gg
Hi Jay,
have you tried with '*' operator in Set Analysis?
For example something like this:
sum({<Set analysis>*<Set analysis> } Field)
Stefano.
Stefano,
I have tried using set analysis and *, but unfortunatley I could not get it to work using variables.
Regards
Jason
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
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
try this jason.
'#' is not rilevant in this case, it works with and without '#'.
C u,
Stefano.
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:
None of the above worked. Do you have any suggestions?
Many thanks for your help.
Jason