# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Valued Contributor II

## Aggregating top 5 values

Hi,

I have some telecom operators and i need to get the nearest 3 of each

DISTINCT

ID_MODULE, //ID TO SELECT

Distancia_Torre_Inativa // Distance

RESIDENT TORRES_INATIVAS

I thought to use Min(Distancia_Torre_Inativa ) Group by CODIGO_OPERADORA_TORRE_INATIVA, but this will bring me 1 value.

So, Each ID_MODULE has a number of CODIGO_OPERADORA_TORRE_INATIVA  with a distance (Distancia_Torre_Inativa ).

Input:

ID MODULE   Operator       Distance

12345               A                    3

54821               B                    8

55154               A                    2

55866               C                    1

15545               A                    5

57781               B                    7

59994               A                    9

43866               C                    6

78821               B                    8

92154               A                    2

44866               C                    1

50545               A                    2

78981               B                    7

51794               A                    8

36866               C                    6

Output:

ID MODULE   Operator       Distance

55154               A                    2

92154               A                    2

50545               A                    2

78981               B                    7

57781               B                    7

54821               B                    8

55866               C                    1

44866               C                    1

43866               C                    6

1 Solution

Accepted Solutions
MVP

## Re: Aggregating top 5 values

try this

Data:

ID MODULE,  Operator,      Distance

12345,              A ,                  3

54821 ,              B ,                  8

55154 ,              A,                    2

55866,              C,                    1

15545,              A,                    5

57781,              B,                    7

59994,              A,                    9

43866,              C ,                  6

78821,              B,                    8

92154,              A,                    2

44866,              C ,                  1

50545,              A,                    2

78981,              B,                    7

51794,              A,                    8

36866,              C ,                  6 ] ;

New:

AutoNumber(RowNo(),Operator) as Rank

Resident Data

Order by Operator,Distance;

DROP Table Data;

Final:

noconcatenate

resident New

where Rank<=3;

drop table New;

7 Replies
Contributor III

## Re: Aggregating top 5 values

Honored Contributor III

## Re: Aggregating top 5 values

Hi Eduardo,

Expression :

If(Rank(-Distance,4,1) <= 3,Distance)

Regards,

Antonio

MVP

## Re: Aggregating top 5 values

Another solution

Data:

ID MODULE,  Operator,      Distance

12345,              A ,                  3

54821 ,              B ,                  8

55154 ,              A,                    2

55866,              C,                    1

15545,              A,                    5

57781,              B,                    7

59994,              A,                    9

43866,              C ,                  6

78821,              B,                    8

92154,              A,                    2

44866,              C ,                  1

50545,              A,                    2

78981,              B,                    7

51794,              A,                    8

36866,              C ,                  6 ] ;

New:

AutoNumber(RowNo(),Operator) as Rank

Resident Data

Order by Operator,Distance;

DROP Table Data;

Expression:

=sum({<Rank={"<=3"}>}Distance)

Valued Contributor II

## Re: Aggregating top 5 values

Hi antonio !

Rank is for Chart and i need to script

Valued Contributor II

## Re: Aggregating top 5 values

Hi Kushal !

Like Antonio this is a Chart solucion, Rank doesn't work to script and im working with maps so a chat solution will keep my maps load to slow and consuming too much memory.

MVP

## Re: Aggregating top 5 values

try this

Data:

ID MODULE,  Operator,      Distance

12345,              A ,                  3

54821 ,              B ,                  8

55154 ,              A,                    2

55866,              C,                    1

15545,              A,                    5

57781,              B,                    7

59994,              A,                    9

43866,              C ,                  6

78821,              B,                    8

92154,              A,                    2

44866,              C ,                  1

50545,              A,                    2

78981,              B,                    7

51794,              A,                    8

36866,              C ,                  6 ] ;

New:

AutoNumber(RowNo(),Operator) as Rank

Resident Data

Order by Operator,Distance;

DROP Table Data;

Final:

noconcatenate

resident New

where Rank<=3;

drop table New;

Valued Contributor II

## Re: Aggregating top 5 values

Hi Kushal, that was very nice solution

Thank you !