topic Controlling sort sequence in QlikView

Controlling sort sequence

Thu, 12 Nov 2009 10:07:39 GMT

I have set of six pie charts on a dashboard that compare data between companies and highlight issues for actions.

The single dimension is a text value, created in a run script, that describes the issue.

I want each pie chart legent to be in the same sequence, regardless of text, frequency, load value etc. I want it to be dependant on the issue severity, but none of the regular sort features gives me what I need.

There are a maximum of 6 dimension values that have known content,

So for example, The values are A B C D E F and I want every pie chart to show the legend in the sequence..

ACDFEB

The only option seems to be the sort expression but the help text gives no guidelines on how to do what I want.

I could put a priority number in front of text in run script i,e,

1A 2C 3D 4F 5E 6B, but I want to avoid that if I can.

Does anybody know how I short format the sort expression to get what I need?

Thanks

Alan Pollard

Controlling sort sequence

Thu, 12 Nov 2009 13:24:41 GMT

you can use mixmatch function in sort expression. Here is explanation from QV Help:

Example: mixmatch( M, 'Jan','Feb','Mar')

returns 1 if M = jan

In your case will be:

mixmatch(YourFieldName, 'A','B','C','D','E','F')

Stefan

Controlling sort sequence

Anonymous — Thu, 12 Nov 2009 13:34:27 GMT

Stefan's approach is a good one but for your sort order:

mixmatch(YourFieldName, 'A','C','D','F','E','B')

Probably unnecessary clarification

Controlling sort sequence

Thu, 12 Nov 2009 14:02:53 GMT

if someone have a problem and he provided with some data example i prefer after explanation to write the solution with his data 🙂 sometimes its unnecessary and for this case i was wake up 5 min before i answer 😄

Controlling sort sequence

Thu, 12 Nov 2009 15:30:35 GMT

Hi Alan ,

Under the Properties->sort tab -> Expression , you can write

if(dimension ='A',1,

if(dimension ='C',2,

if(dimension ='D',3,

if(dimension ='F',4,

if(dimension ='E',5

if(dimension ='B',6

))))))

And then give a descending option for the same.

Hope this helps .

Regards ,

Poorva

Controlling sort sequence

Thu, 12 Nov 2009 15:31:54 GMT

Hi Alan ,

Under the Properties->sort tab -> Expression , you can write

if(dimension ='A',1,

if(dimension ='C',2,

if(dimension ='D',3,

if(dimension ='F',4,

if(dimension ='E',5,

if(dimension ='B',6,

))))))

And then give a ascending option for the same.

Hope this helps .

Regards ,

Poorva

Controlling sort sequence

Fri, 13 Nov 2009 02:31:09 GMT

A big thanks for all the reponses. The mixmatch worked perfectly.

Alan

Controlling sort sequence

johnw — Fri, 13 Nov 2009 05:20:35 GMT

A couple other approaches:

Use the dual() function to associate a numeric value with each text value, then sort in numeric order.
Load the possible text values in their own table in your desired sequence before loading the main data, then sort in original load order.

Controlling sort sequence

Tue, 06 Jul 2010 15:15:17 GMT

I have tried the nested 'if' and the 'mixmatch' option and both work - GREAT, thanks.

But I also prefer John's second suggestion which deals with the issue at source and would prove much more effecient in the long run.

Im now trying to test the same 'mixmatch' on a cyclic dimension, can anyone help.?

I've tried:-

=mixmatch(Dimension1, 'A','C','B', Dimension3, 'one','three','two')

I also tried:-

=mixmatch(Dimension1, 'A','C','B')

=mixmatch(Dimension3, 'one','three','two')

I also tried:-

=mixmatch(Dimension1, 'A','C','B')

OR mixmatch(Dimension3, 'one','three','two')

None of them work on all the dimensions. . . . . . . . .

Can anyone help please.

Thanks

Controlling sort sequence

johnw — Tue, 06 Jul 2010 19:05:53 GMT

I believe it would be like this if you want to stick with that approach:

=mixmatch("Group Name",'A','C','B','one','three','two')

When dealing with a cyclic group, you don't refer to the fields directly, but rather to the group itself. When dimension 1 is selected, it will be compared to the list, and give you a value of 1, 2 or 3 to sort by. When dimension 3 is selected, it will be compared to the list, and give you a value of 4, 5 or 6 to sort by. You could have problems if you can have the same value in more than one field, though, which is a hint to me that this isn't really the right approach, even if you can make it work for your specific case.

Controlling sort sequence

Wed, 07 Jul 2010 15:08:17 GMT

Superb John, many thanks.

Controlling sort sequence

Tue, 20 Jul 2010 08:49:38 GMT

Hello All,

I have been through this post and have found many useful ways of sorting especially the post about sorting within a group dimension. Even so I am struggling with the group I have created in the attached example. The group is multilevel and needs to be sorted as follows:

1> Quarter-Year , needs to be sorted logically by year-quarter

2> TPP - Sort order is HCD,ECD,DCD,SCD (always does not matter what numbers they show except if one is 0)

3> Month - Logical

4> Days of the month - Logical

I have spent several hours trying to test this to be correct. Any help appreciated. MAny thanks.

Controlling sort sequence

Tue, 22 Feb 2011 14:12:43 GMT

Hello,

I have the same kind of requirements. I have a slider, where i want to sort the months based on my requirements. However, The expression is disabled in the sort table, and i cannot sort it using expression. Do you have any idea why is the sort expression disabled? Can you please suggest any otherway to do it

Syed