topic Re: Sorting question in QlikView

Sorting question

Fri, 14 Mar 2014 13:34:58 GMT

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)

Re: Sorting question

Anonymous — Fri, 14 Mar 2014 13:41:11 GMT

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

Re: Sorting question

jonathandienst — Fri, 14 Mar 2014 13:44:11 GMT

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

Re: Sorting question

Fri, 14 Mar 2014 13:45:15 GMT

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

Re: Sorting question

jonathandienst — Fri, 14 Mar 2014 13:47:14 GMT

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,

Re: Sorting question

Anonymous — Fri, 14 Mar 2014 13:51:05 GMT

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