Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I made a simple sample with this script:
Data:
LOAD * Inline [
agent, NumOfSurveys, SCORE
1,1,8
1,2,9
2,1,3
2,2,4
2,3,5
3,1,5
3,2,4
3,3,5
];
agent as dimension and this expression:
=aggr(rank(avg({<agent={"=count(NumOfSurveys)>=3"}>} SCORE)), agent)
returns:
Where are you trying to use this expression? In a text box object or a straight or pivot table? If you are using this in a straight or pivot table what is your dimension?
Can you please share sample data qvw.
I'm using a straight table. The dimension is Agents (the top 5)
Hi Nadav, maybe with this:
aggr(if(count(NumOfSurveys)>=3, rank(avg(SCORE))),agent)
Unfortunately it is a protected network.. can't export anything
Hi Nadav,
Maybe try:
if(aggr(count(NumOfSurveys),agent)>=3,aggr(rank(avg(SCORE)),agent)
I get error in expression
This way, only agents that has more than 3 surveys gets rank, But it still considering those who has less then 3 surveys..