topic Re: Sorting Month Names(Jan, Feb..) in QlikView

Sorting Month Names(Jan, Feb..)

Thu, 24 Jan 2013 14:43:32 GMT

I am looking into last 4 months from today, dat is jan,dec,oct and nov in a pivot table. But I am seeing sorting order as jan,oct,nov,dec. I nned to see oct,nov,dec and jan bcos jan is 2013 so I need that to see at last. Thanks

Re: Sorting Month Names(Jan, Feb..)

adamwilson — Thu, 24 Jan 2013 14:51:52 GMT

that is the correct behavior when sorting by month, consider using MonthName(Date) instead which will add the year to the field so it will sort as Oct 2012, Nov 2012, Dec 2012, Jan 2013

Re: Sorting Month Names(Jan, Feb..)

Thu, 24 Jan 2013 15:35:10 GMT

I have Date field as 1/24/2013 format, I used Date(Date field,'MMM-YY') and converted into Jan-13, but in pivot table when I used this field I am still unable to sort. ...

Re: Sorting Month Names(Jan, Feb..)

adamwilson — Thu, 24 Jan 2013 15:44:24 GMT

is the sort set to numeric value in properties?

Re: Sorting Month Names(Jan, Feb..)

Thu, 24 Jan 2013 15:46:29 GMT

Yes, I did...

Re: Sorting Month Names(Jan, Feb..)

Thu, 24 Jan 2013 15:49:16 GMT

Yes, I did...Please find the attachment

Re: Sorting Month Names(Jan, Feb..)

hic — Thu, 24 Jan 2013 16:04:48 GMT

The first thing you need to do is to interpret the dates as dates. They are not identified as dates now. This is done with the Date#() function. And you should do that before you create the qvd. See more on http://community.qlik.com/docs/DOC-3102.

Then You can create the Months using Month(Date#([DateField],'MMM-YYYY')).

HIC

Re: Sorting Month Names(Jan, Feb..)

adamwilson — Thu, 24 Jan 2013 16:16:31 GMT

Your Field name [Curative TR Month Year] isn't a date datatype, in your script replace:

Month(CURATIVE_TR_HCSENTDTE)&'-'& Year(CURATIVE_TR_HCSENTDTE) as [Curative TR Month Year],

With:

MonthName(CURATIVE_TR_HCSENTDTE) as [Curative TR Month Year],

Re: Sorting Month Names(Jan, Feb..)

Christian_Lauritzen — Mon, 27 Jan 2014 21:50:54 GMT

Pramod,

There is a quite simple solution as an alternative to the above.

=dual(MonthField,Index('janfebmaraprmayjunjulaugsepoctnovdec',lower(MonthField)))

Suppose your MonthField contains Mar, Dec, Jan, Feb ... etc. in an unsorted order. If you enter the formula above as a sort expression, your sequence will turn out right. It is also useful when you use Month(Fieldname) as it can distort the sort order.

Hope this adds new insight!