- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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