Get top three names - Qlik Community

Anonymous · ‎2016-08-18

Hi all,

I have to get top 3 names from a field, but first I have to filter the dataset to max(count) of field A and than max(count) Field B. From this filtered dataset IK have to get the top 3 names from Name field. I am using the below expression to filter the data and get the top 3 names and their count.

The issue I am facing is that this expression is returning me all the names and their count. But I am not able to restrict it to the top three names and count

=Concat({<
[field A] = {"=rank(Count([field A]),4)=1"},
[Field B]={"=rank(count([Field B]))=1"}>}
Aggr(Only( [name]) & ' ('&Count([name])&')', [name], [name],[field A]))

sunny_talwar · ‎2016-08-18

Difficult to say much without taking a peek at your data? Would you be able to provide some raw data?

marcus_sommer · ‎2016-08-18

I think you need to apply the rank-condition within the inner aggregation: Set Analysis in the Aggr function.

- Marcus

ramasaisaksoft · ‎2016-08-18

Hi Fayez,

Is there any chance to you (Dimension Limit) for top 3 ,can u think that way?

i know you can't write set analysis there but you can control .

Anonymous · ‎2016-08-18

Hi

Is it like this ?

=if(Aggr(Rank(COUNT(field A)),name)<=3, 'TOP3NAMES:$(=Concat(DISTINCT name, ', '))', 'Others')

=if(Aggr(Rank(COUNT(field B)),name)<=3, 'TOP3NAMES:$(=Concat(DISTINCT name, ', '))', 'Others')

//Edited

FYI

I think the above expression works only if you select at least 4 or 5 values ....

Anonymous · ‎2016-08-18

No, I am putting the result in text box

Anonymous · ‎2016-08-18

Thanks Sunny,

I have pasted the dummy data below.

First I have to find out which string or value has been used max no of times. In this case it will be Net Sales. So I have to filter this data to Net Sales. After this, in the filtered dataset I have to find out which value has been used max no of times in Field A. In this case it will be General Industries. So I will have to filter the filtered dataset further to General Industries. Now my data set will have General Industries in Field A , Net Sales In Field B, and

these

Reagle,Daniel K.

Wickline,Samuel Joel

Planert,Tina M

Reagle,Daniel K.

Wells,Kristen Michelle

in the name field.

I have to get the top 3 names in this field along with their count. Like Wickline,Samuel Joel (3),Reagle,Daniel K.(2),Planert,Tina M(1).

I will ignore Wells,Kristen Michelle, as I have got Planert,Tina M as the third name. So effectly it doesn't matter which 1 I take, I just have to limit the overall names to 3 persons.

Hope I could explain it to you.

So when I use below expression it does not return me the correct results because Name is ranked in the complete data set rather than the filtered datset.

Name	Rank
Sullivan,Steven P	2
Sullivan,Steven P	2
Sullivan,Steven P	2
Reagle,Daniel K.	1
Reagle,Daniel K.	1
Wickline,Samuel Joel	3
Wickline,Samuel Joel	3
Wickline,Samuel Joel	3
Planert,Tina M	4
Planert,Tina M	4
Reagle,Daniel K.	1
Reagle,Daniel K.	1
Reagle,Daniel K.	1
Reagle,Daniel K.	1
Reagle,Daniel K.	1
Wickline,Samuel Joel	3
Valentino Pence,Geoffrey Brett	5
Valentino Pence,Geoffrey Brett	5
Wells,Kristen Michelle	6

=Concat({<
[field A] = {"=rank(Count([field A]),4)=1"},
[Field B]={"=rank(count([Field B]))=1"}>} ,[Name]={"=rank(count(name))<=3"}>}
Aggr(Only( [name]) & ' ('&Count([name])&')', [name], [name],[field A]))

Field A	Field B	Name
Generic Template	Net Sales	Sullivan,Steven P
Generic Template	Net Sales	Sullivan,Steven P
Generic Template	Net Sales	Sullivan,Steven P
Generic Template	Net Sales	Reagle,Daniel K.
General Industries	Net Sales	Reagle,Daniel K.
General Industries	Net Sales	Wickline,Samuel Joel
General Industries	Net Sales	Wickline,Samuel Joel
General Industries	Net Sales	Wickline,Samuel Joel
General Industries	Net Sales	Planert,Tina M
Middle Market	Interest	Planert,Tina M
Middle Market	Interest	Reagle,Daniel K.
Middle Market	Interest	Reagle,Daniel K.
Middle Market	Interest	Reagle,Daniel K.
Middle Market	Interest	Reagle,Daniel K.
Middle Market	Interest	Reagle,Daniel K.
Middle Market	Interest	Wickline,Samuel Joel
Middle Market	Interest	Valentino Pence,Geoffrey Brett
General Industries	Net Sales	Reagle,Daniel K.
General Industries	Net Sales	Wells,Kristen Michelle