I spent all day Friday trying to figure out how to benchmark with respect to a peer set, ignoring the chart dimensions. In my situation, I have an organization hierarchy, consisting of 7 levels, with level 7 being the lowest level. My challenge is to rank the selected member within level 7 and rank her against her peers (i.e. all other level 7 members sharing the same level 6 manager), for the current Question.
So my chart consists of one dimension and one expression: The dimension is my question (e.g. I have 10 questions I'm benchmarking for), the expression is my relative rank.
What makes this tricky (or nonintuitive) in QlikView is that the "rank" function will only rank across the chart dimensions. There is no way I have found getting around this. In fact, although the tool-tips show an option for group fields (e.g. total <fld1, fld2>, ...) I don't believe that total fields will ever work.
After sifting through all discussion on the matter, I was not able to find a solution to this problem. However, after some considerable thinking I have found a working solution that also has the added benefit of performing well - better perhaps than the rank function.
The "trick" to solving this problem comes from realizing that you don't need to actually sort the values - you only need to count the number of peer values greater than your selected member value.
I have pasted code below showing how this can be achieved. To keep things simple, I am using a variable called PeerSet. I am able to change the value of PeerSet depending on what level I am within the hierarchy, by way of a trigger. In fact in my situation, I also need to manipulate the chart expression itself, but for now you don't need to worry about those details.
Here is the PeerSet definition:
PeerSet = {<MemberName_Level7= P({1<MemberName_Level6>} MemberName_Level7) - MemberName_Level7>} Here is the expression definition:
sum($(PeerSet)
aggr(
if(
avg($(PeerSet) FT_FormulaValue) >
avg(total <Question_Description>
aggr(avg(FT_FormulaValue), MemberName_Level7, Question_Description)
),
1
),
MemberName_Level7, Question_Description
)
) + 1
One last note: The code from above is a distilled version of my actual code. I have not tested it, but it should work.
