Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
alanwong1178
Contributor III
Contributor III

Sorting in Pivot table

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

 

alanwong1178_0-1635126427411.png

 

Labels (3)
12 Replies
abhijitnalekar
Specialist II
Specialist II

Hi @alanwong1178 ,

 

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

Regards,
Abhijit
keep Qliking...
alanwong1178
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

Hi @alanwong1178 ,

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

 

Regards,
Abhijit
keep Qliking...
alanwong1178
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

Hi @alanwong1178 ,

Please find attached QVF file for your reference.

 

abhijitnalekar_0-1635147376781.png

 

 

Regards,
Abhijit
keep Qliking...
AshutoshBhumkar
Partner
Partner

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
Partner
Partner

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
Specialist
Specialist

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 namecountry))

 

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 

NitinK7_0-1635173827463.png

 

 

alanwong1178
Contributor III
Contributor III
Author

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