topic Re: aggr + rank + set analysis? in QlikView

aggr + rank + set analysis?

Thu, 08 Oct 2015 08:52:26 GMT

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

Re: aggr + rank + set analysis?

sunny_talwar — Thu, 08 Oct 2015 13:03:11 GMT

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?

Re: aggr + rank + set analysis?

Thu, 08 Oct 2015 13:08:44 GMT

Can you please share sample data qvw.

Re: aggr + rank + set analysis?

Thu, 08 Oct 2015 13:09:06 GMT

I'm using a straight table. The dimension is Agents (the top 5)

Re: aggr + rank + set analysis?

rubenmarin — Thu, 08 Oct 2015 13:10:57 GMT

Hi Nadav, maybe with this:

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

Re: aggr + rank + set analysis?

Thu, 08 Oct 2015 13:11:04 GMT

Unfortunately it is a protected network.. can't export anything

Re: aggr + rank + set analysis?

Anonymous — Thu, 08 Oct 2015 13:20:14 GMT

Hi Nadav,

Maybe try:

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

Re: aggr + rank + set analysis?

Thu, 08 Oct 2015 13:23:45 GMT

I get error in expression

Re: aggr + rank + set analysis?

rubenmarin — Thu, 08 Oct 2015 13:24:11 GMT

Re: aggr + rank + set analysis?

Thu, 08 Oct 2015 13:25:44 GMT

This way, only agents that has more than 3 surveys gets rank, But it still considering those who has less then 3 surveys..

Re: aggr + rank + set analysis?

Thu, 08 Oct 2015 13:29:33 GMT

no results

Re: aggr + rank + set analysis?

rubenmarin — Thu, 08 Oct 2015 14:21:34 GMT

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:

Re: aggr + rank + set analysis?

Thu, 08 Oct 2015 14:33:33 GMT

Perfect. it works now!! !

I only missed the equal sign before the count func.

Thank you!!

Re: aggr + rank + set analysis?

Anonymous — Thu, 08 Oct 2015 14:40:16 GMT

hi,

aggr(rank(avg({<agent = {"=count(NumOfSurveys)>=3"}>}SCORE)),agent)