# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Contributor II

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

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.

Tags (3)
4 Replies
MVP

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

Could you create a qvw with sample data and share?

Valued Contributor II

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

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))

Contributor II

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

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

Contributor II

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

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.

Community Browser