Sorting in Pivot table

[alanwong1178]

As I bulit a pivot table  with multiple measure like the example below.

It is possible to make Qlik Sense to sort by Profit measure  in all layer which means client with most profit at the top

and its country with most profitable at the top  of the subgroup

and most profitable product at the top of the subgroup

[abhijitnalekar]

Hi @alanwong1178 ,

Have you tried Sort by an expression like for product:- aggr(Sum(Profit),Product) 

[alanwong1178]

You expression works when there is only one dimension for the pivot table. If there are multiple dimension it would not work.  Also , it is based on the ranking of the total profit for each product rather then the profit within each country for each client

What I would like to have is that : 

For example , Pivot table can show the most profitable client eg. Client A on the top  and least profitable at the bottom.

Then it rank the most profitable country within Client A at the top and least profitable at the bottom. 

The it rank the most profitable product at the top for each country and least profitable at the bottom

[abhijitnalekar]

Hi @alanwong1178 ,

Please add your measures in the sort by expression for all the dimensions which are included in the pivot table.

[alanwong1178]

I have added expression in all dimension and it does not work.

Please provide your dataset and example and result or screenshot.

Like I said before , the ordering of product based on the ranking of the total profit for each product rather than the profit within each country for each client. That's why it end up with weird ordering  for both product and country.

If I remove all dimension and keep product only, it works fine.

[abhijitnalekar]

Hi @alanwong1178 ,

Please find attached QVF file for your reference.

[AshutoshBhumkar]

Hello,

Maybe you can try below expression in each of the dimension to sort.

RANK(The expression of the measure on which you want the dimension values to be sorted).

E.g : Rank(Sum(Profit))

Thanks,

Ashutosh

[shivanisapkale]

Hello,

Make profit as your first measure and then in Sorting option select sort by measure in desc it will sort the data according to Profit.

Regards,

Shivani sapkale

[NitinK7]

Please follow below steps

1. Add the below script in your script editor.

//for 2 dimension in Pivot table

Set v_Sort2= Dual($1, Num#(Left(Keepchar(Hash256($1,$2),'0123456789ABCDEF'),18),'(hex)'));    

//for 3 dimension in Pivot table
Set v_Sort3= Dual($1, Num#(Left(Keepchar(Hash256($1,$2,$3),'0123456789ABCDEF'),18),'(hex)'));

//for 4 dimension in Pivot table
Set v_Sort4= Dual($1, Num#(Left(Keepchar(Hash256($1,$2,$3,$4),'0123456789ABCDEF'),18),'(hex)'));

////for 5 dimension in Pivot table
Set v_Sort5= Dual($1, Num#(Left(Keepchar(Hash256($1,$2,$3,$4,$5),'0123456789ABCDEF'),18),'(hex)'));

Next step to go to the your pivot table

add first dimension as it is , in your case may be client name

add second dimension like 

=$(v_Sort2(client name,country)

add third dimension like 

=$(v_Sort3(product, client name, country))

then next step go the sorting section ->turn off custom sort->enable sort by expression-> select descending

and add your profit expression as it is  for all the dimension 

[alanwong1178]

May i know if this solution can carter for user who may change the order of the dimension in pivot table . As you may notice, user can drag the dimension to change their order in pivot table