4 Replies Latest reply: Oct 31, 2014 3:23 PM by Darrin Pilkington RSS

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

    Darrin Pilkington

      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.

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

          Could you create a qvw with sample data and share?

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

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

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

                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 Anjos with 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