Top Performers; Set Analysis and Aggr Question

[richard_pearce6]

Hi ,

Question if I may, I'm using this 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 can't for the life of me understand why I can't remove the [Name] selection .

Can anyonehelp?

Richard

See Post: http://community.qlik.com/docs/DOC-5187

[MK_QSL · Accepted solution]

You can use below alternative...

Only({1<Name={"=Rank(SUM({1}Value))=1"}>} Name)

Only({1<Name={"=Rank(SUM({1}Value))=2"}>} Name)

Only({1<Name={"=Rank(SUM({1}Value))=3"}>} Name)

Only({1<Name={"=Rank(SUM({1}Value))=4"}>} Name)

Only({1<Name={"=Rank(SUM({1}Value))=5"}>} Name)

Or uses below in case of same SUM(Values)

=

Only({1<Name={"=Rank(SUM({1}Value),4)=1"}>} Name)

Only({1<Name={"=Rank(SUM({1}Value),4)=2"}>} Name) 

Only({1<Name={"=Rank(SUM({1}Value),4)=3"}>} Name)

Only({1<Name={"=Rank(SUM({1}Value),4)=4"}>} Name)

Only({1<Name={"=Rank(SUM({1}Value),4)=5"}>} Name)

[richard_pearce6]

Thanks Manich. I used your suggestion and modified to

=Only({<Name={"=Rank(sum({<Name=>}[Value]),4)=1"}>} Name)

=Only({<Name={"=Rank(sum({<Name=>}[Value]),4)=2"}>} Name)

=Only({<Name={"=Rank(sum({<Name=>}[Value]),4)=3"}>} Name)

=Only({<Name={"=Rank(sum({<Name=>}[Value]),4)=4"}>} Name)

=Only({<Name={"=Rank(sum({<Name=>}[Value]),4)=5"}>} Name)

I wanted to ensure when Name was selected it was disregarded in the expression but other selections were possible.

Many thanks for your prompt reply

Richard

[#4]

Hi I'm struggling with identifying a top performer and don't seem to be able to make it work.

I'm using the current statement to identify the total count of NPS surveys:

Count({$<Period_seq={$(=max(Period_seq))},Department = {'CE'}>} NPS)

I want to identify which Agent has the highest score - with the result being the name of the Agent.

thanks