topic Re: Sort Dates in Jan, Feb, Mar in QlikView

Sort Dates in Jan, Feb, Mar

Thu, 05 Sep 2013 12:47:15 GMT

Hi there,

This is my 1st post so I hope im not doing anything wrong!

Im importing some data with the column labelled "Month" and the data in there is in text as Jan, Feb, Mar, etc but when I try and put this into a pivot table it comes out as Apr, Aug, Dec .....

Is there a way so I can sort this in the correct order?

Thanks in adavance

Mark

Re: Sort Dates in Jan, Feb, Mar

swuehl — Thu, 05 Sep 2013 12:57:02 GMT

Best approach is to create your Month values as a dual that you can sort numerical:

LOAD

Month(Date#(MonthField,'MMM')) as MonthField,

MonthField being your field containing your month values.

Re: Sort Dates in Jan, Feb, Mar

Thu, 05 Sep 2013 13:03:47 GMT

Thanks for the quick reply.

Unforntunatly its pulling the info from a database I cant change. Is what Im looking for is another column called "year" and I want that going down the left hand side of the pivot table then have the months going across the top.

Is there work around that can be used to achive this, somehing like the sort by custom lists in Excel?

Re: Re: Sort Dates in Jan, Feb, Mar

Thu, 05 Sep 2013 13:04:35 GMT

I always use a inline table to assign a numeric value to the months:

MESI:

LOAD * INLINE [

MONTH, MESE_NUM

jan,1

feb,2

mar,3

apr,4

may,5

jun,6

jul,7

aug,8

sep,9

oct,10

nov,11

dec,12

];

And then sort by numeric value on charts/Tables/Listboxs

Cheers

Juan Pedro

Re: Sort Dates in Jan, Feb, Mar

swuehl — Thu, 05 Sep 2013 13:06:28 GMT

You can use a sort expression in sort tab for your dimension field:

=match(MonthField, 'Jan', Feb','Mar',...)

Re: Re: Sort Dates in Jan, Feb, Mar

Thu, 05 Sep 2013 13:15:12 GMT

Thanks for the reply Juan, Im very new to this so I'm not sure how to use the code you've give me correctly?

Re: Sort Dates in Jan, Feb, Mar

Thu, 05 Sep 2013 13:15:44 GMT

Thanks for this it seems to be working!

Re: Sort Dates in Jan, Feb, Mar

Andrea_Ghirardello — Thu, 05 Sep 2013 13:42:36 GMT

If your original Month field contains values according to the MonthNames variable in your QlikView document, you only need to configure the month sort order in your pivot as number ascending.

This because Month become a dual field.

If you need to understand the type of a field, you can see the tags in the field properties (Ctrl+Alt+D and find Month field in the Table tab).

See attached example

Re: Sort Dates in Jan, Feb, Mar

Thu, 05 Sep 2013 14:32:29 GMT

Is a Inline Table so should be inserted in your load script.

Since you are new this group may content some usefull guides/examples for your formation.

http://community.qlik.com/groups/qlikview-academic-program

Cheers,

Juan Pedro

Re: Sort Dates in Jan, Feb, Mar

Thu, 05 Sep 2013 14:35:08 GMT

Thanks both, Im going to be in meetings the rest of the day so I will take a look tomorow but thanks again for you help