topic Re: List months in calander date order in QlikView

List months in calander date order

Wed, 22 Jan 2014 16:56:57 GMT

Hi,

This little thing is really starting to bug me. I have a list box of months and I want them in calander date order, can anyone advise how to get this??

Many thanks,

Sara

Re: List months in calander date order

Colin-Albert — Wed, 22 Jan 2014 16:58:06 GMT

Sort the month field in numeric order.

The data is held as a dual value that combines text and a hidden number.

Use num(month) to see the numeric value.

Re: List months in calander date order

maxgro — Wed, 22 Jan 2014 16:59:07 GMT

did you try to order by numeric value in sort tab

Re: List months in calander date order

alexandros17 — Wed, 22 Jan 2014 17:00:19 GMT

The best way to manage months is with the relative year for example dec 2013 is 201312, in this way you can sort by year and by month, can you explain better the type of data you have?

Thanks

Re: List months in calander date order

Wed, 22 Jan 2014 17:09:05 GMT

Tried those but doesn't seem to work. The table I have is MONTH_CREATED which is pulling through the month in a text format from a CRM database.

It only seems to like sorting it in alphabetical order........is there an expression that can be used?

Re: List months in calander date order

Clever_Anjos — Wed, 22 Jan 2014 17:11:10 GMT

Use a date#(MONTH_CREATED,'FORMAT') as sorting expression

Re: List months in calander date order

maxgro — Wed, 22 Jan 2014 17:28:13 GMT

last resort, but i prefer Clever Anjos solution is sort by expression

= match(MONTH_CREATED, 'JAN', 'FEB', ........., 'DEC')

Re: List months in calander date order

ecolomer — Wed, 03 Sep 2014 18:39:10 GMT

Use ordenation by expresion: ... Num(Month) .... because is the number of month

Re: List months in calander date order

MarcoWedel — Wed, 03 Sep 2014 21:26:09 GMT

Hi,

you said it yourself: your month is loaded as text thus QlikView doesn't recognize it as a month value.

So instead of

SQL SELECT

...,

MONTH_CREATED,

...

FROM your DB;

try with

LOAD
     ...,
     Month(Date#(MONTH_CREATED, 'MMM')) as MONTH_CREATED,
     ...
;
SQL SELECT
    ...,
    MONTH_CREATED,
    ...,
FROM your DB;

Re: List months in calander date order

maleksafa — Thu, 04 Sep 2014 10:58:50 GMT

use the dual function when loading the month in your script to assign for each month a number representing the order (Jan having number 1, Feb 2, March 3 etc...) and then when you want to sort the month, go to sort tab and sort it by numeric value.

Re: List months in calander date order

ashfaq_haseeb — Thu, 04 Sep 2014 11:03:32 GMT

Hi,

Try this in your sort tab expression.

=Dual("Your Month Field",match("Your Month Field" ,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))

Regards

ASHFAQ