Pivot table multiple dimension ranking

[alanwong1178]

I have created a pivot table showing the top 5 client with top 5 country based on their sales or cost or profit

Variable has been created so user can select either rank by sakes or cost or profit

Expression for client:

=IF ( Aggr ( Rank ( SUM(vmeasure)), client ) <= 5 ,client )

Expression for Country:

=IF ( Aggr ( Rank ( SUM(vmeasure)),Client, Country ) <= 5 , Country )

As you know that the sequence of dimension can be changed when user drag the dimension on the top left corner of the pivot table.

If user drag country to the right which become the first dimension and client become the secondary dimension . Things and data turns into mess .

Any solution if country become the first dimension , system change the expression automatically to:

Expression for Country:

=IF ( Aggr ( Rank ( SUM(vmeasure))Country ) <= 5 , Country )

Expression for client:

=IF ( Aggr ( Rank ( SUM(vmeasure)) client,Country ) <= 5 , client )

Therefore , this gives the correct result of top 5 country with top 5 client for each country.

or freeze the drag and drop dimension option in pivot table?

[BenjaminT]

Hi, have you tried limiting the number of dimension values to 5 within the dimension settings (with Limitation = 'Fixed Number')?

You would then only need to have Sum(Sales) as your measure and the sorting should work as you need it to, regardless of which dimension is first.

Is there a reason you cannot do it this way?

Note: in the Sorting properties, you would need to check the 'Sort by first measure' option

Ben

[BenjaminT]

Otherwise, if you need to limit to 5 within the expression with Aggr() and Rank(), do you definitely need to aggregate over Country in your Client expression?

What if the client expression was simply:

=IF ( Aggr ( Rank ( SUM(Sales)) client) <= 5 , client )

Does this produce what you need?

Ben

[alanwong1178]

Hi Benjamin,

I updated the post.. Please refer to question above.

It is because I have multiple measure. I would like to make it looks dynamic for user to select either rank by sales or cost or profit. Therefore, limitation is not the way to solve my problem. Also , rank by first measure is not the option as well.

[BenjaminT]

That makes sense. How about:

client expression:

=IF ( Aggr ( Rank ( SUM(Sales)) client) <= 5 , client )

Country expression:

=IF ( Aggr ( Rank ( SUM(Sales)) Country) <= 5 , Country )

This worked for me without needing to add a second dimension into the Aggr() function at all.

[alanwong1178]

I have tried your suggestion before but end up with "wrong result" . You may refer to my other post as below. Or you may kindly provide any other way to tackle the problem. Thanks.

https://community.qlik.com/t5/New-to-Qlik-Sense/Top-N-in-Pivot-table-multiple-dimension/m-p/1852327#...