topic Re: Sort by expression whatever the selection in QlikView

Sort by expression whatever the selection

Fri, 25 Apr 2014 18:44:45 GMT

Hello,

I have a table which contains one column "VALUE":

NAME	ORDER
A4	4
B2	2
C3	3
D1	1

I do not control the load order and I want name to be sorted like this:

I have tried both proposals from Creating a custom sort order » The Qlik Fix! The Qlik Fix!

Filter box: (State = Ascending because I want user selection to be on top)

Sort by Match()

Expression = Ascending and equals to match(NAME, 'D', 'B', 'C', 'A')

It works fine if I have no selection. If I filter on C3, I should have

C3 (on top because of State)

but I get:

Sort by ORDER column

Expression = Ascending and equals to ORDER

Result is the same

Graph:

Dimension = NAME

Expression = 1

I only sort by expression ascending, State is not checked

It works fine if I have no selection. If I filter on C3, my x-axis should be:

D1 > B2 > C3 > A4

but I get:

A4 > B2 > D1 > C3

I believe this is because selecting C3, the other values are NULL hence, cannot be ordered so it is probably done randomly.

How can I make sure I always get my order respected whatever the selection made (selection being on top in a filter box)

I attached the test app.

Thanks,

Re: Sort by expression whatever the selection

mambi — Fri, 25 Apr 2014 21:30:47 GMT

Hi,

i think if you want to resolve your problem you should use the "load order"; so change your load script to this :

LOAD * INLINE [
     NAME, ORDER
     D, 1
     B, 2
     C, 3
     A, 4
];

then in tour list box-> sort tab : State : Ascending ,Load order : original

Re: Sort by expression whatever the selection

Sat, 26 Apr 2014 12:58:17 GMT

I have put a very simple example to ease understanding but the real table I am using is not INLINE, it is coming from a DB so your proposal won't work unfortunately.

That may work if was able to load the DB table with the order I need, but I do not know how to do that if it is not INLINE.

Thanks,

Re: Sort by expression whatever the selection

tresB — Sat, 26 Apr 2014 13:04:29 GMT

Use set ananlysis to disregard selection. Use sort expression like:

=match(Only({1}NAME), 'D', 'B', 'C', 'A')

Re: Sort by expression whatever the selection

mambi — Sat, 26 Apr 2014 13:21:51 GMT

try it using order by clause in your query

Re: Sort by expression whatever the selection

preminqlik — Sat, 26 Apr 2014 13:37:42 GMT

hi try this

only({1}order)

only({1}match(NAME, 'D', 'B', 'C', 'A'))

Re: Sort by expression whatever the selection

Joseph_Musekura — Sat, 26 Apr 2014 13:54:42 GMT

Hi,

A list box that is sorted by an expression loses the sort order when a selection is made in the list box.

You need to sort by expression over the entire data set and not only the currently selected data set, thus you need to use a set of expression that include the desired data set (see attached example)

Regards,

Re: Sort by expression whatever the selection

Wed, 28 May 2014 10:50:00 GMT

Hi,

I am looking for a solution for custom sort order in a graph that has cyclical dimension containing several fields that each require a custom sort order (as numerical/alphabetical sorting is not meaningful). In simple words, I have

Cyclical Group Dimension Field: Stage

Values: Good, Better, Stunning

Cyclical Group Dimension Field: Forecast

Values: Never, Guess, Likely, Sure

neither of the following sort expressions allow me to have above sorting orders in both fields when I swap from one to another in my graph's cyclical dimension button:

=match([Stage], 'Good', 'Better', 'Stunning') or match([Forecast],'Never', 'Guess', 'Likely, 'Sure')

=> only Stage is sorted

=match([Stage], 'Good', 'Better', 'Stunning')

=match([Forecast],'Never', 'Guess', 'Likely, 'Sure')

=> only Stage is sorted

what would be solutions in this case?

Many thanks in advance.

Re: Sort by expression whatever the selection

kaylaramsey2 — Tue, 04 Jun 2019 14:24:55 GMT

This answer was very helpful. I was having an issue where my most recent 3 months would be out of order when I selected a different year. The only{1} option worked great. Thank you.