Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

darrin_pilkingt
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
MVP

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

Could you create a qvw with sample data and share?

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

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

Sample.jpg

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

NameFollowupsAvgRankRank %Standing
4241124.74-
Joseph1608804.00191.18%80-100
Shannon548548.00288.24%80-100
James338169.00385.29%80-100
Pita254254.00482.35%80-100
Richard132132.00579.41%20-80
Emidy131131.00676.47%20-80
Kiel121121.00773.53%20-80
Diana100100.00870.59%20-80
Clara9898.00967.65%20-80
Stephanie8787.001064.71%20-80
Taylor8484.001161.76%20-80
Tiffany8383.001258.82%20-80
Bryan7979.001355.88%20-80
Abram6565.0014-1652.94%20-80
Ana6565.0014-1652.94%20-80
Laura6532.5014-1652.94%20-80
Leah5959.001744.12%20-80
Darren5858.001841.18%20-80
David3939.001938.24%20-80
Daniel3737.002035.29%20-80
Ashley3535.002132.35%20-80
Kris2626.002229.41%20-80
Andrew2525.002326.47%20-80
Romeo2222.002423.53%20-80
Mario2020.002520.59%20-80
Michael1818.0026-2714.71%0-20
Thedrea1818.0026-2714.71%0-20
Katrina1111.002811.76%0-20
Ashlea99.00298.82%0-20
Jaimie55.00305.88%0-20
Gary11.00312.94%0-20

Thank you

darrin_pilkingt
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.