topic Sorting Issue in QlikView

Sorting Issue

Mon, 30 Apr 2012 21:13:11 GMT

Hi,

I have a report that loading Month values.

On the script I have :

CASE

WHEN Month(H.#SOBIL) = 1 THEN 'Jan'

WHEN Month(H.#SOBIL) = 2 THEN 'Feb'

WHEN Month(H.#SOBIL) = 3 THEN 'Mar'

WHEN Month(H.#SOBIL) = 4 THEN 'Apr'

WHEN Month(H.#SOBIL) = 5 THEN 'May'

WHEN Month(H.#SOBIL) = 6 THEN 'Jun'

WHEN Month(H.#SOBIL) = 7 THEN 'Jul'

WHEN Month(H.#SOBIL) = 8 THEN 'Aug'

WHEN Month(H.#SOBIL) = 9 THEN 'Sep'

WHEN Month(H.#SOBIL) = 10 THEN 'Oct'

WHEN Month(H.#SOBIL) = 11 THEN 'Nov'

WHEN Month(H.#SOBIL) = 12 THEN 'Dec'

END AS MONTH,

Now when I sort by Load Order It shows up in a random order.

Jul, Aug, Oct, Nov, Mar, Feb...ets even the data is in order

How can I fix it to show up the Months in order starting by Jul, Aug, Sep, Oct....

Thxs

Sorting Issue

swuehl — Mon, 30 Apr 2012 22:00:01 GMT

Month(Date) results in a dual with a text representation, so this should be enough to get your month short names anyway.

For the sort order, try a sort expression like

=match(month(DATEFIELD),'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun')

Sorting Issue

Mon, 30 Apr 2012 22:06:58 GMT

Hi,

Thank you for your help.

I have tried that by adding the formula in the Sort Expression but it didn't work.

Sorting Issue

swuehl — Mon, 30 Apr 2012 22:09:41 GMT

Do you have a script definition different from

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Sorting Issue

Mon, 30 Apr 2012 22:14:23 GMT

That's exactly what I have.

Regards,

Sorting Issue

swuehl — Mon, 30 Apr 2012 22:26:00 GMT

Ah yes, I believe it should be

=match(MONTH,'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun')

with MONTH being your month field.

Sorting Issue

Mon, 30 Apr 2012 22:38:35 GMT

I guess I have figured it out.

I deleted the dimenssion and added it again and it seems to work fine using your formula.

Thxs,