Sorting of Months

[montubhardwaj]

Hi All,

I have a problem with sorting of Months (used as Dimension). The Months are of Current Quarter and are Dynamic i.e. in Quarter 4, Month on the Dimension axis will be Oct-10, Nov-10, and Dec-10.

Everything is working fine except that the Month are not sorted Properly. I tried many ways but no luck [:'(]

Please see below for the problem. Many thanks in advance.

Regards,

Sharma

[Miguel_Angel_Baeyens]

Hello Sharma,

Depending on how you have created the date field, you may sort by "Numeric value", "Ascending".

Regards

[kji]

If not numeric you might be able to use the "Load Order" sort option.

[montubhardwaj]

HI Baeyens,

None of these options are working out.Actually it is a calculated dimensions achieved as follows:

=if(Quarter=$(CurrentQtr),Pick(Month,'Jan','Feb','March','April','May','June','July','Aug','Sep','Oct','Nov','Dec')&'-'& Right($(CurrentYear),2))

I think this is causing some issue but not able to figure it out.

@ Johan: As I am using a calculated dimension, I am not able to sort it by Load Order

[#5]

Hi Sharma,

With this statement you are creating strings (text).

Pick(Month,'Jan','Feb','March','April','May','June','July','Aug','Sep','Oct','Nov','Dec')&'-'& Right($(CurrentYear) will create a string.

You have to create a date from the months, if you have the numeric month in a seperate field you could use makedate(day,month,year)!

You might even change your sort order (Ascending/Descending)... but that won' t fit for all months.

Regards

Martin

[martin59]

Hi,

You can try to sort by "expression" ascending, your expression will be something like :

only(Month)

where Month is your numeric field

[montubhardwaj]

Hi Martin,

Thx for giving it a shot. But this did not help me. [:'(]

[montubhardwaj]

Hi Martin,

The field "Month" is a Numeric field only having values from 1-12.

[montubhardwaj]

Hi All,

"Month" is a Numeric field having values from 1-12. Now I have changed the Dimension as follow:

=if(Per_Quarter=$(CurrentQtr),Month )

Now it is sorting the months in right order as shown below.

But I want the column heading as Jul-10, Aug-10, Sep-10 instead of 7,8 and 9.

Please let me know if you have any suggestions.

Regards,

Sharma

[#10]

Sharma

I would look at replacing Month with using the function MonthEnd. This way you have a real date (so you can sort) and you can format is you require mmm-yyyy, mm or whatever.

See the help there are lots of date functions listed that might give you other ideas.

Cheers