Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Could you create a qvw with sample data and share?
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))
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
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.