Sorting question

[#1]

Hello,

I have a couple of date list boxes which are as follows:

Year - YEAR_ACTIVITY

Month - MONTH_ACTIVITY (sorted by expression MONTH_ACTIVITY_NUM)

Day - MONTHDAY_ACTIVITY

The month/day is shown as JAN(01) JAN(02) etc.  However when sorting this it does not load in month and numberical order.  (As you can see below).

Can anyone advise on how to load this in month and numerical order, JAN(01) JAN(02) etc?

(If it helps, it is loaded in the script as to_char(vat.EVTDATE,'MON(DD)') as MONTHDAY_ACTIVITY)     

[Anonymous · Accepted solution]

Doesn't sort by text A->Z help? It seems to work just fine on my end unless i'm missing something.

[jonathandienst]

Hi

Rather bring in vat.EVTDATE into Qlikview as a date, and use the Month() function to create the month as a dual value and the Day() function to get the day number. To create a month/day field, create a dual value with the month/day as text and the actual date as the value. Assuming you passed EVTDATE into your model, you could use:

    LOAD

          ...

          Date(EVTDATE) As DATE_ACTIVITY,

          Month(EVTDATE) As MONTH_ACTIVITY,

          Dual(Month(EVTDATE) & '(' & Day(EVTDATE) & ')', EVTDATE) As MONTHDAY_ACTIVITY,

          ...

HTH

Jonathan

[#4]

I must have tried everything apart from that however I am sure I did!  It's working now! (Feel a bit silly for asking such a simple question).

Thank you!!

[jonathandienst]

Its possible you dont want the year of the date to affect the month/day field. If so, change the line to

     Dual(Month(EVTDATE) & '(' & Day(EVTDATE) & ')', MakeDate(1900, Month(EVTDATE), Day(EVTDATE))) As MONTHDAY_ACTIVITY,

[Anonymous]

we have all been there! i'm glad you got that sorted out.