topic Re: Month/Year date field in date sort order in QlikView

Month/Year date field in date sort order

zagzebski — Wed, 02 Oct 2013 01:45:40 GMT

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:

Re: Month/Year date field in date sort order

jagan — Wed, 02 Oct 2013 02:14:13 GMT

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.

Re: Month/Year date field in date sort order

tresB — Wed, 02 Oct 2013 06:14:49 GMT

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.

Re: Month/Year date field in date sort order

preminqlik — Wed, 02 Oct 2013 06:36:58 GMT

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;

Re: Month/Year date field in date sort order

v_iyyappan — Wed, 02 Oct 2013 06:42:43 GMT

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,

Re: Month/Year date field in date sort order

Wed, 12 Jul 2017 16:51:49 GMT

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