Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adias102
Contributor III
Contributor III

How to sort dimension values according to another dimension in Qlik Sense

Hi all,

I have a bar chart with 1 dimension - Stations.
I have another dimension named Area.
The 2 tables, on which the dimensions are based, are linked. Each station is linked to one area.
for example: 

Station Area sort
A North  
B South  
C West 2
D West 3
E North  
F West 1
G South  
H East  
I North  

 

I want to sort the stations according to the chosen area. If you choose only 'West' I want to see the stations according to the sort column - F, C, D. If you choose more than 1 area or any other area I want to see the stations alphabetically A,B,C,D,E,....

How can I do that?

Labels (1)
3 Solutions

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @adias102 

You are wanting to sort the station based on an expression. That expression will want to be something like:

=if(count(distinct Area) = 1, sort, ord(Station))

This relies on the Station only having one character, or the first character being unique. If it is multiple characters you need to calculate a number to sort on based on multiple characters from station, e.g.:

=if(count(distinct Area) = 1, sort, (ord(mid(Station,1,1))*100)+ord(mid(Station,2,1)))

If you have values that are not unique after two characters you will need to multiply the first character by 10000, second by 100 etc.

Hope that helps.

Steve

View solution in original post

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @adias102 

Sorry, I assume you were meaning any single Area, in that case:

=if(only(Area) = 'West', sort, (ord(mid(Station,1,1))*10000)+(ord(mid(Station,2,1))*100)+ord(mid(Station,3,1)))

This assumes you have a column called sort, if you don't then the sort part needs to change to only use Station:

=if(only(Area) = 'West', match(Station, 'F','C','D'), (ord(mid(Station,1,1))*10000)+(ord(mid(Station,2,1))*100)+ord(mid(Station,3,1)))

Hopefully that gets you where you need to be.

Steve

View solution in original post

adias102
Contributor III
Contributor III
Author

Hi,

Thank you for the suggestions. Somehow, they didn't work.
Finally I found this solution and it works:

if(GetFieldSelections(Area)='West', Order2, Order1)

When
Area is the dimension
Order1 and Order2 are 2 columns in my Station table

View solution in original post

4 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @adias102 

You are wanting to sort the station based on an expression. That expression will want to be something like:

=if(count(distinct Area) = 1, sort, ord(Station))

This relies on the Station only having one character, or the first character being unique. If it is multiple characters you need to calculate a number to sort on based on multiple characters from station, e.g.:

=if(count(distinct Area) = 1, sort, (ord(mid(Station,1,1))*100)+ord(mid(Station,2,1)))

If you have values that are not unique after two characters you will need to multiply the first character by 10000, second by 100 etc.

Hope that helps.

Steve

adias102
Contributor III
Contributor III
Author

Hi,

It's not what I meant, or I didn't understand the solution.

If you choose the are 'West' and only this value I want to see the 3 related station in this order F,C,D.

But, if you choose several areas (West & North) or any other area (South) I want to see the stations in alphabetical order.

The actual stations' names have multiple characters and the first character is not unique.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @adias102 

Sorry, I assume you were meaning any single Area, in that case:

=if(only(Area) = 'West', sort, (ord(mid(Station,1,1))*10000)+(ord(mid(Station,2,1))*100)+ord(mid(Station,3,1)))

This assumes you have a column called sort, if you don't then the sort part needs to change to only use Station:

=if(only(Area) = 'West', match(Station, 'F','C','D'), (ord(mid(Station,1,1))*10000)+(ord(mid(Station,2,1))*100)+ord(mid(Station,3,1)))

Hopefully that gets you where you need to be.

Steve

adias102
Contributor III
Contributor III
Author

Hi,

Thank you for the suggestions. Somehow, they didn't work.
Finally I found this solution and it works:

if(GetFieldSelections(Area)='West', Order2, Order1)

When
Area is the dimension
Order1 and Order2 are 2 columns in my Station table