Date not sorted correctly

[Anonymous]

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!

[devarasu07]

Hi,

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

[vishsaggi]

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.

[Anonymous]

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?

[martinpohl]

to order on numeric value check this.

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

[rubenmarin]

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.

[Anonymous]

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.

[Anonymous]

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]

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

[uroboros]

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