Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
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)
• ### General Question

3 Solutions

Accepted Solutions

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

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

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

4 Replies

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

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.

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

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

Tags
Community Browser