Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andyscslots
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

I look forward to your answers.

1 Solution

Accepted Solutions
OmarBenSalem
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)

Capture.PNG

7 Replies
OmarBenSalem
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)

Capture.PNG

thiago_justen
Valued Contributor III

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

Maybe this will help you:


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
OmarBenSalem
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:

load * Inline [

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

];

thiago_justen
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
JustinDallas
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?

andyscslots
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

 

MFGDCIPU
WMS1000
BALLY900
IGT-

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

andyscslots
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

Community Browser