Sorting Issue

[#1]

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

[swuehl]

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')

[#3]

Hi,

Thank you for your help.

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

[swuehl]

Do you have a script definition different from

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

?

[#5]

That's exactly what I have.

Regards,

[swuehl · Accepted solution]

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.

[#7]

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,