Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
sidneylu
Contributor III

Date not sorted correctly

Hello,

I am sure this question has been asked before but I can't seem to find the answer.  I have

a selection box which contains dates in the following format:

01-DEC-2017

15-DEC-2017

22-NOV-2017

How do I get these values to be sorted in time chronological order?

Thanks!

Tags (1)
25 Replies
devarasu07
Honored Contributor II

Re: Date not sorted correctly

Hi,

by default qlikview sort by auto ascending order. try to uncheck the load order in sort tab

Capture.JPG

vishsaggi
Esteemed Contributor III

Re: Date not sorted correctly

What is that you are expecting to see and where you want to see this order? Is this your right date format?

May be you can try like:

LOAD *, Num(Floor(Date#(DateField, 'DD-MMM-YYYY'))) AS NewDateField INLINE [

DateField

01-DEC-2017

15-DEC-2017

22-NOV-2017

];

And use this NewDateField in sorting expression ASC or DESC.

sidneylu
Contributor III

Re: Date not sorted correctly

My "Load Order" is not checked.

I want these values to be populated in time order.  With the above samples, I want the list to

look like:

22-NOV-2017

01-DEC-2017

15-DEC-2017

Is this even possible?   I was reading on the DUAL() function.  It seems to work well if you have pure

text that you want to sort.  Since my values contain integers and texts, not sure if this doable?

Any suggestions?

Partner
Partner

Re: Date not sorted correctly

to order on numeric value check this.

a date field is always a dual field (DD.MMM.YYYY and numeric value)

Re: Date not sorted correctly

Hi Sydney, you can try adding the field to a simple listbox, if it's left-aligned it means that is a string, and dates should be numbers (right aligned by default), in this case you can use Vishwarath solution.

You can also use Date() instead of Num() if you want to load this field in a user-friendly format and as a number, so kind of auto-sortable.

sidneylu
Contributor III

Re: Date not sorted correctly

Vishwarath & Ruben,

Thanks!

I was able to get the sort I want using:

Date(Floor(Date#(AsOf, 'DD-MMM-YYYY')))

Now let me populate with more dates from the db and see if they all come out correctly.

sidneylu
Contributor III

Re: Date not sorted correctly

Though the dates are sorted correctly now using


Date(Floor(Date#(AsOf, 'DD-MMM-YYYY')))


but they come out as


11/22/2017

12/01/2017

12/15/2017


How do I format it so they will come out as?


22-NOV-2017

01-DEC-2017

15-DEC-2017


vishsaggi
Esteemed Contributor III

Re: Date not sorted correctly

You have do this for that. That is what i mentioned in my earlier script to create a newdatefield

Like

Date(Floor(Date#(AsOf, 'DD-MMM-YYYY')), 'DD-MMM-YYYY') AS youroriginalField


Num(Floor(Date#(AsOf, 'DD-MMM-YYYY')))   AS yourSortDateField.


So when you use your listbox go to sort tab then in the expression add this

NewSortDateField as ASC. Like below

Capture.PNG

uroboros
Contributor

Re: Date not sorted correctly

If you want to keep the format you have dated, you must specify in the variable of short date in this way:


  • SET DateFormat='DD/MMM/YYYY';


Next up, cast your date format with this way:


  • Date#(DateField) as NewDateField,


With it you have all ready to load the date as you need, verifiy with the date be a dual value, how do it, it will be alling to the rigth.


Regards.

Miguel