Date Ordering

[#1]

Hi,

I submitted a question early about formatting a date field as a new field with format mm-yyyy

I got a response which worked which was

Create a field like below in your table.

Month(Date_Field) & '/' & Year(Date_Field) AS MonthYear

so that you can use the MonthYear field in your graphs as dimensions.

However, when i load them new field in a list box, i can not sort the dates in order. I want it to be jan-11, feb-11 ..... jan-12 ....., the best i can get is jan-11, jan-12, feb -11, feb-12!

Any help?

Thanks

Laura

[ekech_infomotio]

if you don't want to mess with dates and ordering, you can use the format YYYY-MM but with two digit-numeric months (2012-01, 2012-02,... 2012-12 )

No more messing - it just works without further finetuning 😉

Greetings,

Edgar

[#3]

The problem with that is that it doesnt group the data i have :

jan-11

jan-11

jan-11

feb-11

feb-11

mar-11

apr-11

apr-11

apr-11

if that makes sense?

Thanks

Laura

[vijay_iitkgp]

Hi,

If you use Monthname(Date) it will result you Jan-2012,Feb-2012 etc and you can be able to sort it.

[kamalqlik]

date(monthstart($(Date_Field) + rowno() - 1), 'MMM-YYYY') AS MonthYear.

Try this it will give you dyanamic results

regards

Kamal

[#6]

Hi Laurahowarth,

The Best Practice is to create a Calender than have a Date Field else create a Date Field and Map that Date Field with Your Fact Table.

For your Problem here is the Solution...

1st Step: try to create a Date column (DD-MM-YYYY)

2nd Step: FromThat Date Create Your Field through this Logic below

date#( trim(Month(Date) & '-' & RIGHT(year(Date),2)),'MMM-YY') as YearMonth,

Now when you Use this YearMonth Colum this will be Automatically Sorted as You want.

============================

else you can create a MonthNum and in Front end you can Sort that column with MonthNum Column in Sot Tab

Hope this work