Month/Year date field in date sort order

[zagzebski]

I have a Month/Year like:

Apr-13

Aug-13

Jan-13

Sept-13

etc.

No matter what I have tried I can't get this to sort in date order. Seems like it should be simple but it keeps doing it alphabetically and sometimes in an order I don't even understand.

Any help?

Thanks, Steve:

[jagan]

Hi Steve,

If you arriving this month dimension from datefield then try like this

LOAD

*,

Dual(Date(DateFieldName, 'MMM-YY'), MonthEnd(DateFieldName)) AS MonthName

FROM TableName;

Now in Sort tab select Number and Asc/Desc as your wish.

Hope this helps you.

Regards,

jagan.

[tresesco]

My suggestion would be to use MonthName() function in the script to generate real date field with numerical data at the back end. If you prefer to work at the front end, then try using date#() function in the sort tab->expression, like:

=Date#(YourMonthYearField, 'MMM-YY')

and then order acs/desc.

[preminqlik]

  LET varMinDate = Num(MakeDate(2001,1,1));

LET varMaxDate = Num(MakeDate(2007,12,31));

LET vToday = Num(today());

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

  $(varMinDate)+RowNo()-1 AS Num,

  Date($(varMinDate)+RowNo()-1,'DD/MM/YYYY') AS TempDate

//AUTOGENERATE($(varMaxDate)-$(varMinDate)+1);

AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************

MasterCalendar:

LOAD Date(TempDate,'DD/MM/YYYY') AS %CommonCalendarLink,

     week(TempDate) AS Week,

     year(TempDate) AS Year,

     QuarterName(TempDate) AS QuarterName,

     if(left(QuarterName(TempDate),7)='Jan-Mar','Q1',

      if(left(QuarterName(TempDate),7)='Apr-Jun','Q2',

      if(left(QuarterName(TempDate),7)='Jul-Sep','Q3','Q4'))) as Quarter,

     month(TempDate) AS Month,

     day(TempDate) AS Day,

     weekday(TempDate) AS WeekDay,

     date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     week(TempDate)&'-'&Year(TempDate) AS WeekYear,

     Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

  Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

RESIDENT TempCalendar 

ORDER BY TempDate Asc;

Drop table TempCalendar;

[v_iyyappan]

Hi,

It can be done both script and design.

USE THE DUAL FUNCTION

          The Dual function can often be used to solve trickier problems. The Dual function lets you

specify both a numeric value as well as which text to associate with this value.

     For instance, if you want to use the week number, but sorted correctly also over the change

of the year, then you should use the date number as numeric value but display the week

number, optionally together with the year:

For example :

Dual( Week( Date ), WeekStart( Date ) ) as YearWeek

Dimension:

Dual(Date(DateFieldName, MonthEnd(DateFieldName))    

Sort tab:

Numeric Value : Asc

Note : DateFieldName is MMM-YY format otherwise use like this Dual(Date(DateFieldName, 'MMM-YY'), MonthEnd(DateFieldName))

Hope its help,

Regards,

[#6]

This solution helped me with my problem for sorting the date value in MMM-YYYY text format by using the DUAL function which Iyyappan mentioned above.

thank you