Multi Box Sorting problem

[#1]

I have a situation when I need many different calendars, Like GMT, EST, User specific TZ.

For each of the calendars' fields there are a prefixes like "GMT_", "EST_".

There is a prefix field in the system like:

PrefixTable:
load * inline [
TZPrefixName,TZPrefix
GMT,GMT_
EST,EST_
Session TZ,Session_
Station TZ,Station_
];

There are variables for current calendar fields presentation, like:

SET vCurrTZPrefix=TZPrefix;

SET vDateTime = "=$(vCurrTZPrefix) & 'DateTime'";
SET vDMDate = "=$(vCurrTZPrefix) & 'DMDate'";
SET vDMYear = "=$(vCurrTZPrefix) & 'DMYear'";
SET vDMMonth = "=$(vCurrTZPrefix) & 'DMMonth'";
SET vDMWeekDay = "=$(vCurrTZPrefix) & 'DMWeekDay'";
SET vDMDay = "=$(vCurrTZPrefix) & 'DMDay'";
SET vDMHour = "=$(vCurrTZPrefix) & 'DMHour'";
SET vDMMinute = "=$(vCurrTZPrefix) & 'DMMinute'";
SET vHourCount = "=$(vCurrTZPrefix) & 'HourCount'";
SET vStartDT = "=$(vCurrTZPrefix) & 'StartDate'";
SET vEndDT = "=$(vCurrTZPrefix) & 'EndDate'";

In a dashboard, there is a Multi Box with the expressions instead of real fields. Everything works fine. There is one problem only - sorting of values in the multi box expression based fields. Instead of expected sequence for week days: Mon, Tue, Wed, Thu, Fri, Sat, Sun; I have got: Wed,Thu,Fri,Sat,Sun, Mon,Tue. There is same problem with a month days.

To understand what I'm talking about just take a look at my attached example, where I've made an emulator of the situation.

Is there any idea how to deal with the sorting, or may be there is another solution for whole approach?

QV version is 9.00.7440.8 SR3 x64

[#2]

So, nobody knows how to deal with it?

[#3]

Does it mean there is no solution for this? Or nobody faced the problem?

[johnw]

Works fine for me, version 8.50.6231.5:

[#5]

John Witherspoon wrote:
Works fine for me, version 8.50.6231.5:
<div></div>

This one works with me too. Try another one. The problem with this:

And this:

[#6]

It seems it isn't possible to sort on a calculated expression in a Multibox. See e.g.

http://community.qlik.com/forums/p/22103/84509.aspx#84509

[#7]

It's not a solution for me. because there is no particular dimension for the multi box. The expression is based on an end user selection.

[johnw · Accepted solution]

Nick Bor wrote:This one works with me too. Try another one. The problem with this:

Oh! That second multi-box doesn't even show in 8.5, so I thought you were talking about the first one. Sorry about my confusion. I can see it in 9.0.

Why it won't let you select a sort order, I don't know. Hopefully that will be a future feature.

Fortunately, it's easy to work around. The sort order appears to be the load order. So all you need to do is load each of these fields in the order you want it presented BEFORE you get to your main load. In other words, just build a sort order table, then drop it at the end. I've done that many times for various reasons, and it appears to work fine here. See attached.

[Sort Orders]:
LOAD recno() as DMDay
AUTOGENERATE 31
;
CONCATENATE ([Sort Orders])
LOAD recno() as DMMonth
AUTOGENERATE 12
;
CONCATENATE ([Sort Orders])
LOAD weekday(date#(20100131,'YYYYMMDD')+recno()) as DMWeekDay
AUTOGENERATE 7
;

... your regular load here ...

DROP TABLE [Sort Orders];

[#9]

That's nice.

It doesn't work in version 10 beta2 either.

Thank you again.

[#10]

Wait a second. Where are the months' names?