# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## limiting and ranking data on a simple table based on a dimension and a measure

I have a table with 400 rows of data with the "DCIPU" column showing different values and the "MFG" column repeating the same names listed below based on the occurrence of the dimension

I want to create 2 tables:

1- limit the table to vview the 10 highest values for the DCIPU regardless of the MFG dimension

2 - Show the top 10 performing values of DCIPU for each of the different MFG dimensions

MFG

DCIPU
WMS1100
Bally2100
IGT1100
Aristocrat1200
EGT80
Ainsworth5

I tried to use the Top 10 dimension function and sorting by DCIPU but it returned the DCIPU values from highest to lowest value and all 400 rows of data instead of limiting it to 10

1 Solution

Accepted Solutions
Esteemed Contributor

## Re: limiting and ranking data on a simple table based on a dimension and a measure

If I fully understand:

1) in ur table, put one dimension as follow:

=aggr(if(rank(DCIPU ,-DCIPU )<=10, DCIPU ),DCIPU )

and uncheck show null values

2) dim1:

dim2: =aggr(if(rank(DCIPU ,-DCIPU )<=10, DCIPU ),MFG,DCIPU )

and uncheck show null values

result (per analogy)

7 Replies
Esteemed Contributor

## Re: limiting and ranking data on a simple table based on a dimension and a measure

If I fully understand:

1) in ur table, put one dimension as follow:

=aggr(if(rank(DCIPU ,-DCIPU )<=10, DCIPU ),DCIPU )

and uncheck show null values

2) dim1:

dim2: =aggr(if(rank(DCIPU ,-DCIPU )<=10, DCIPU ),MFG,DCIPU )

and uncheck show null values

result (per analogy)

Valued Contributor III

## Re: limiting and ranking data on a simple table based on a dimension and a measure

SUM({<YOUR_DIMENSION={"=RANK(SUM(YOUR_MEASURE))<=10"}>} YOUR_MEASURE)

or

Only({<YOUR_DIMENSION={'=rank(sum(YOUR_MEASURE))<=10'}>}YOUR_DIMENSION)

Cheers

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Esteemed Contributor

## Re: limiting and ranking data on a simple table based on a dimension and a measure

that won't do it thiago :

try my expressions and urs with this inline table, u'll see what happens in each case:

dim, mesure

1, 200

1, 300

1, 400

1, 500

1, 600

2,300

2,400

2,500

2,600

2,700

a,500

a,600

a,700

a,800

a,900

a,100

b,600

b,700

b,400

b,300

b,900

];

Valued Contributor III

## Re: limiting and ranking data on a simple table based on a dimension and a measure

You're absolutelly right! My mistake...

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Valued Contributor II

## Re: limiting and ranking data on a simple table based on a dimension and a measure

Omar, what does the Rank(something,-something) do?

New Contributor III

## Re: limiting and ranking data on a simple table based on a dimension and a measure

Hi Omar,

the 2 expressions work but have a common problem as the remainder of the rows appear on the table with a '-' symbol in place of a value for example if I was to show the top 2 the following is showing, and it continues for the remaining 397 rows

 MFG DCIPU WMS 1000 BALLY 900 IGT -

and I unchecked  show the null values. Am I missing a step?

New Contributor III

## Re: limiting and ranking data on a simple table based on a dimension and a measure

Hi Omar,

The solution works, The was an extra step I didn't know about which was going to the Add-ons tab and unchecking the 'include zero values' box. Thanks for your help