Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: List months in calander date order

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.

MVP
MVP

Re: List months in calander date order

did you try to order by numeric value in sort tab

Re: List months in calander date order

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

Re: List months in calander date order

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


Employee
Employee

Re: List months in calander date order

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

MVP
MVP

Re: List months in calander date order

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

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

ecolomer
Honored Contributor II

Re: List months in calander date order

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

Re: List months in calander date order

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
Valued Contributor

Re: List months in calander date order

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.

Community Browser