3 Replies Latest reply on Nov 15, 2015 9:49 AM by Gerardo Varela

    Quantile (Quintile) a metric without it being effected by filtering

    paul traynor

      I have employee/agent data and I am trying to quintile by the amount of hours or frequency they are spending on tasks.  I am able to get it to work using the following calculation I found in another post:

      IF (INDEX()/SIZE()) > .8 then "5th Quintile"

      ELSEIF (INDEX()/SIZE()) > .6 then "4th Quintile"

      ELSEIF (INDEX()/SIZE()) > .4 then "3rd Quintile"

      ELSEIF (INDEX()/SIZE()) > .2 then "2nd Quintile"

      ELSE "1st Quintile" end

       

      and then doing a table calculation restarting every Department Code and sort it based on the metric I want to quintile.

       

      My issue is that when I filter on a specific employee it changes the quitiles.  I tried adding the employee to level of detail but that doesn't seem to work.  I've also tried Level of Detail functions like FIXED but they won't work with table calculations that I am using (eg INDEX and SIZE).  I've seen the post the Richard Leeke (Table Calculation: Quantile (version 2))has done and I haven't been able to get that to work either.

       

      Any one have an idea how to do this?