Pulling a value out of Rank to score other agents against?

[Anonymous]

I need to rank agents and identify the top 20% and bottom 20%.  I need the minimum value of the top 20% rank to be the value I can score other agents against.  Using the sample data below as an example.

The top 20% value is Saves = 18 as the % is below 20%, so all agents with Saves >= 18 would get a score of 2.

The bottom value is Saves = 9 as the % is below the 80%, so all agents with Saves < 9 would get a score or 0, all others 1.

I am doing this by selecting departments for the overall rank and then selecting a single agent to get their score against the groups defined values as above.

In addition, I need to place this value into a text box to display a 0, 1 or 2 for the agent selected.

AgentName     Saves     %                    Score

Bob                    22          0.00%               2

Sarah                 19          12%                  2

Tony                   18          19%                 2

Craig                  16          37%                  1

Tom                    14          52%                 1

Lynn                    9           78%                 1

George                3           83%                 0

Sherrie                1           96%                 0        

Thank you.

[tresesco]

Could you create a qvw with sample data and share?

[danieloberbilli]

In case that your %-Column is calculated dynamically then maybe like this: please see qvw attached. A straight table with Agent as dimension and the following expression:

if(Percent<=0.2, 2,

if(Percent>=0.8, 0, 1))

[Anonymous]

I have figured out how to get the correct ranking percentile I was looking for with this.  I am now trying to take this data a bit further.  Below is a sample portion of my scorecard.  What I am trying to perform with the values I have from my chart below. 

I need to pull the single Rank % value for an agent as you can see in Productivity below. 

I did find this article.  Re: Show Single Rank Value from Clever Anjoswith this formula

='Bob : ' & only({<Name={"Bob"}>}aggr(rank(Sales),Name))

Rank %

Rank(TOTAL - sum(FollowUps))/

count(TOTAL aggr(sum(FollowUps), CGID))

Standing

if(

Rank(TOTAL - sum(FollowUpCounter))/

count(TOTAL aggr(sum(FollowUpCounter), Name)) >= .8, '80-100',

     if(

     Rank(TOTAL - sum(FollowUpCounter))/

     count(TOTAL aggr(sum(FollowUpCounter), Name)) <= .2, '0-20',

     '20-80'))

Name	Followups	Avg	Rank	Rank %	Standing
	4241	124.74	-
Joseph	1608	804.00	1	91.18%	80-100
Shannon	548	548.00	2	88.24%	80-100
James	338	169.00	3	85.29%	80-100
Pita	254	254.00	4	82.35%	80-100
Richard	132	132.00	5	79.41%	20-80
Emidy	131	131.00	6	76.47%	20-80
Kiel	121	121.00	7	73.53%	20-80
Diana	100	100.00	8	70.59%	20-80
Clara	98	98.00	9	67.65%	20-80
Stephanie	87	87.00	10	64.71%	20-80
Taylor	84	84.00	11	61.76%	20-80
Tiffany	83	83.00	12	58.82%	20-80
Bryan	79	79.00	13	55.88%	20-80
Abram	65	65.00	14-16	52.94%	20-80
Ana	65	65.00	14-16	52.94%	20-80
Laura	65	32.50	14-16	52.94%	20-80
Leah	59	59.00	17	44.12%	20-80
Darren	58	58.00	18	41.18%	20-80
David	39	39.00	19	38.24%	20-80
Daniel	37	37.00	20	35.29%	20-80
Ashley	35	35.00	21	32.35%	20-80
Kris	26	26.00	22	29.41%	20-80
Andrew	25	25.00	23	26.47%	20-80
Romeo	22	22.00	24	23.53%	20-80
Mario	20	20.00	25	20.59%	20-80
Michael	18	18.00	26-27	14.71%	0-20
Thedrea	18	18.00	26-27	14.71%	0-20
Katrina	11	11.00	28	11.76%	0-20
Ashlea	9	9.00	29	8.82%	0-20
Jaimie	5	5.00	30	5.88%	0-20
Gary	1	1.00	31	2.94%	0-20

Thank you

[Anonymous]

I ended up calculating the average percentages for each agent for the period and from that pulled the 80 and 20 percentiles which I then fed into variables which I could use to in a text field formula.