Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

List months in calander date order


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

10 Replies
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
MVP
MVP

did you try to order by numeric value in sort tab

alexandros17
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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?

Untitled.png


Clever_Anjos
Employee
Employee

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

maxgro
MVP
MVP

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

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

ecolomer
Master II
Master II

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:

QlikCommunity_Thread_131xxx_Pic1.JPG.jpg

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
Specialist
Specialist

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.