Controlling sort sequence

[#1]

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

[#2 · Accepted solution]

Hi

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

[Anonymous]

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

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

Probably unnecessary clarification

[#4]

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 😄

[#5]

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

[#6]

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

[#7]

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

Alan

[johnw]

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.

[#9]

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

[johnw]

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.