Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Show Top Performers

Show Top Performers

This document describes a number of different way of selecting top performers - within a group, with an additional condition, etc... Originally posted as a blog, the article shows the common pitfalls and suggest practical solutions for a variety of complex issues related to limiting the list to a certain number of top items.

Comments
Not applicable

Hi Oleg,

thanks for the contribution, very interesting as usual.

I've tried to use the Set Analysis option (=sum({<Item = {“=rank(sum(Sales),4)<=1000”}>} Sales)), but I receive back an error: Error in set modifier ad hoc element list.

It's the first time I found this message on the expression window.

What's wrong?

Thank you

Luca

Luminary
Luminary

Hi Oleg,

thanks for the post. Question if I may, I'm using your expression to generate text boxes which have actions to select in field.

=aggr(if(rank( sum( [Value]),3)=1,Name),Name)

I have 5 boxes for the 5 best performers for value. It works fine, I click the text box which makes a 'Select in Field' for Name.

Once there the ranking doesn't work for anything higher than 1 because the data is filtered.

I've tried a looking at various combinations of set analysis to remove the Name field selection so the boxes stay intact:

=aggr(if(rank( sum( {<Name=>} [Value]),3)=1,Name),Name)

or even

=aggr(if(rank( sum( {1} [Value]),3)=1,Name),Name)

I've also tried putting the whole lot in an only() with Section access and tried applying TOTAL to the functions that I can. I understand your expression but can't for the life of me understand why I can't remove the [Name] selection .

Can you help?

Richard

Hi Richard,

I can't devote too much time to delve into it, but I suspect that the problem is caused by the AGGR function that's already receiving a limited set of data, which can't be widened inside of the AGGR. I'd try something like this:

only( {<Name=>} aggr(if(rank( sum( {<Name=>} [Value]),3)=1,Name),Name))


Ignoring the Name selection outside of the AGGR should help in this case. This is the one of many intricacies of Set Analysis and AGGR that I teach in my Set Analysis session at the Masters Summit for QlikView.


cheers,


Oleg Troyansky

http://naturalsynergies.com

http://masterssummit.com


Not applicable

Oleg, Thank you for this great post. It has been very helpful.

I am hoping you could help me take it one step further. I have a table that displays the top 5 performers based on the sum of their score for the year (IM_SCORE). I now need to separately display in a text box how much the sum of these 5 items represent in relation to the whole population. So what I am really need assistance with is coming up with a dynamic formula that would display the sum of the top 5 performers, without the need for a table. Once I have that, it will be simple to just divide by the total for my percentage value.

Thank you in advance!

Gustavo

Hi Gustavo,

for this problem, I'd recommend a slightly different approach - selecting the top 5 using the Advanced Search capability in Set Analysis. You would filter the items based on their rank. Something along these lines:

sum({<Item={"=rank(sum(Score), 4)<=5"}>} Score)

cheers,

Oleg Troyansky

Not applicable

Thank you so much. This is exactly what I was looking for.

Best,

Gustavo

Not applicable

oleg, i was wondering if you could help me refine my dimension limits. I need to show the last 30 occurrences from the date selected. That may mean more than 1 occurrence in 1 day so not necessarily >DateSelected-30.

I attempted the rank set analysis scenario but that counts unique dates and I end up getting more than the 30 rows i want.

I also used the dimension limits in the chart, 1st expression has the date (not shown, but enabled). theres 4 dimensions and 4th is the date as well. I put the dimension limit on the date dimension.

do you have any suggestions or any thoughts on what i may be doing wrong?

It's hard for me to imagine what you may be doing wrong... I'd try refining your rank example, using another field - if you have more than one occurrence per day, use the timestamp instead of a date to get the last 30.

Not applicable

that did the trick! thank you.

Version history
Revision #:
1 of 1
Last update:
‎10-21-2013 04:37 PM
Updated by: