Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Sorting question

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)     

1 Solution

Accepted Solutions
jsaradhi
Valued Contributor

Re: Sorting question

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

test-sort.png

5 Replies
jsaradhi
Valued Contributor

Re: Sorting question

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

test-sort.png

MVP
MVP

Re: Sorting question

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Sorting question

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

MVP
MVP

Re: Sorting question

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,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jsaradhi
Valued Contributor

Re: Sorting question

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

Community Browser