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

[Anonymous]

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
WMS	1100
Bally	2100
IGT	1100
Aristocrat	1200
EGT	80
Ainsworth	5

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.

[OmarBenSalem · Accepted solution]

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)

[Thiago_Justen_]

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

[OmarBenSalem]

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_]

You're absolutelly right! My mistake...

[JustinDallas]

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

[Anonymous]

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?

[Anonymous]

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