13 Replies Latest reply: Oct 8, 2015 10:34 AM by Nadav Luboshitz RSS

    aggr + rank + set analysis?

      Hi everyone,

      I've been struggling about this problem for long time, maybe someone can help. it's a hard one to explain but i'll try:

       

      I'm trying to build a table that ranks agents by their performance in "main measure" and presents the best 5.

      I'm using the expression : aggr(rank(SCORE),agent) and filtering only agent that has rank lower or equal to 5.

      In addition, I want to present score and rank of another measure (scores in surveys) for these 5 agents . However, agents that has less than 3 surveys in the period chosen, shouldn't be considered in the rank.

      For example, if there are 2 agents: first one got average of 100 with 2 surveys and second got 90 with 3 surveys, the first one won't get any rank and the second will get the first place.


      I've tried this:

      aggr(rank(avg(SCORE)),if(count(NumOfSurveys)>=3,agent))

      But i get no results.

      I thought of using Set Analysis but haven't figure out how


      Does someone has an idea?


      Thanks!

      Nathan