2 Replies Latest reply on Dec 3, 2018 4:22 AM by Zhouyi Zhang

    Ranking of SalesForce people

    Milosz Lesnik

      Dear Friends,

      I am trying to make a ranking of SaleForce people that consider 7 KPI. For each KPI I created a formula that gives points for % of realization and it looks like that:

       

      FLOAT(IF   [KPI1]>=1.1 THEN "6"

      ELSEIF  [KPI1]<1.1 and [KPI1] >=1 THEN "5.5"

      ELSEIF  [KPI1]<1 and [KPI1] >=0.95 THEN "5"

      ELSEIF  [KPI1]<0.95 and [KPI1] >=0.9 THEN "4.5"

      ELSEIF  [KPI1]<0.9 and [KPI1] >=0.85 THEN "4"

      ELSEIF  [KPI1]<0.85 and [KPI1] >=0.8 THEN "3.5"

      ELSEIF  [KPI1]<0.8 and [KPI1] >=0.75 THEN "3"

      ELSEIF  [KPI1]<0.75 and [KPI1] >=0.7 THEN "2.5"

      ELSEIF  [KPI1]<0.7 and [KPI1] >=0.65 THEN "2"

      ELSEIF  [KPI1]<0.65 and [KPI1] >=0.6 THEN "1.5"

      ELSEIF  [KPI1]<0.6 and [KPI1] >=0.55 THEN "1"

      ELSEIF  [KPI1]<0.55 and [KPI1] >=0.5 THEN "0.5"

      else "0" END)

       

      and for other KPI formula is the same just the KPI changes...

       

      The formula returns me good results when I pick ONE MONTH but what I want to do is to SUM points from each KPI per MONTH and then make an AVG. I can't do that formula because it says that the data is already aggregated and I cannot aggregate if further. I tried to do it like that:

      ([KPI1}+[KPI2}.......+[KPI7})/7 and then it works but when I select two months on filter then Tableau counts it bad. It does it like that - first of all it counts the realization of two months together and then it gives points from formula. What should I do to make Tableau first give points per month and then in the next step sum these points per month and make avg of them.

       

      Anyone help me please?

       

      MiƂosz.

        • 1. Re: Ranking of SalesForce people
          Sasha Hanna

          Hi Milosz,

           

          In your calculation you are performing a row level comparison whereas what you are after is the aggregate level. You can wrap the aggregation around your measures, for example:  SUM([KPI]). Additionally you don't have to return a string (denoted by quotations) for each label and wrap that around with a float function but directly return the float. I've done a small example with sample superstore to help illustrate:

           

          Let me know if this helps or you need further guidance.

           

          Cheers,

          Sasha Hanna

          Certified Tableau & Alteryx Trainer, The Information Lab

           

          • 2. Re: Ranking of SalesForce people
            Zhouyi Zhang

            Hi, Milosz

             

            without seeing your workbook, it is hard to provide a accurate solution, it seems your KPI is already an aggregation calculation, so probably you could try

             

            ([KPI1}+[KPI2}.......+[KPI7})/7*countd(month([your date field]))

             

            Hope this helps

             

            ZZ