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

[adias102]

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?

[stevedark · Accepted solution]

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]

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 · Accepted solution]

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 · Accepted solution]

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