List months in calander date order

[#1]

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

[Colin-Albert]

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.

[maxgro]

did you try to order by numeric value in sort tab

[alexandros17]

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

[#5]

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?

[Clever_Anjos]

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

[maxgro]

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

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

[ecolomer]

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

[MarcoWedel]

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;

see also the demo app attached:

If you don't correct the load script, you will have to implement some of the former proposed sorting expressions to correct this behaviour in each and every use case of this field !

hope this helps

regards

Marco

[maleksafa]

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.