Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ruben_moya
Contributor III
Contributor III

Sort measure in pivot table with multiple dimensions

Hi experts,

 

I haven't been able to find the perfect solution to my problem in the different forums.

 

Ive got the following table:

Hotels:

Load*Inline [


country, city, chain, name, sales
España, Barcelona, NH, hotel1, 9000
España, Madrid, NH, hotel2, 12000
España, Madrid, Ibis, hotel3, 6500
España, Bilbao, Melia, hotel4, 7500
Italia, Turin, Ibis, hotel5, 20000
Italia, Milan, NH, hotel6, 3500
Italia, Roma, Ibis, hotel7, 19000
];

 

 

I need my the measure Sum(sales) to be sorted by different dimensions: 

ie: first by country, then by chain.

 

I have found the follwoing formulas in forums:

Sort dimension 1

Aggr(sum(sales), [country] )

Sort dimension2

Aggr(sum(sales), [chain] )

 

The main problem here, as you can see in the picture, is that the the chains of each country are sorted by the total amount of the chain, indpendently from the measure country.

Don't hesitate to ask for questions in case I havent explained myself properly.

Thanks in advanced

Labels (2)
2 Replies
Akshesh_Patel
Support
Support

hello,

What is the expected result you want to display, I will see if I can produce the desired output from the given data.

Regards,

akki

 

ruben_moya
Contributor III
Contributor III
Author

Hi Akki, thanks a lot for your answer.

That was quite a simple example. I've got 4 dropwdown lists where the user can select different fields.

If the user selects 'country', the values in the field should be sorted by sales and country; 

if the user selects country and city, the values should be sorted first by country, and then within the country, sorted by city;

country,city ,chain ,name , sales
Italia ,Turin ,Ibis ,hotel5, 20000
Italia ,Roma ,Ibis ,hotel7, 19000
Italia ,Milan ,NH ,hotel6, 3500
España ,Madrid ,NH ,hotel2, 12000
España ,Barcelona, NH ,hotel1, 9000
España ,Bilbao ,Melia ,hotel4, 7500
España ,Madrid ,Ibis ,hotel3, 6500

 

Thanks