topic Re: Date not sorted correctly in QlikView

Date not sorted correctly

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Date not sorted correctly

devarasu07 — Mon, 18 Dec 2017 19:37:24 GMT

Hi,

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

Re: Date not sorted correctly

vishsaggi — Mon, 18 Dec 2017 19:46:04 GMT

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.

Re: Date not sorted correctly

Anonymous — Mon, 18 Dec 2017 20:03:05 GMT

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?

Re: Date not sorted correctly

martinpohl — Mon, 18 Dec 2017 20:06:01 GMT

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

rubenmarin — Mon, 18 Dec 2017 20:06:44 GMT

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.

Re: Date not sorted correctly

Anonymous — Mon, 18 Dec 2017 20:14:40 GMT

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.

Re: Date not sorted correctly

Anonymous — Mon, 18 Dec 2017 20:17:21 GMT

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

Re: Date not sorted correctly

vishsaggi — Mon, 18 Dec 2017 20:27:19 GMT

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

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

Re: Date not sorted correctly

uroboros — Mon, 18 Dec 2017 20:34:41 GMT

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

Re: Date not sorted correctly

Anonymous — Mon, 18 Dec 2017 20:43:44 GMT

Vishwarath

OK. I followed your steps. Everything looks correct when the screen is first loaded. However after I select "01-NOV-2017", this values goes to the bottom of the list. Is this expected? I don't want to the values to shift. See my screenshot.

Re: Date not sorted correctly

Anonymous — Mon, 18 Dec 2017 20:50:49 GMT

This is how I configured the sort. Did I miss anything?

Re: Date not sorted correctly

vishsaggi — Mon, 18 Dec 2017 20:51:16 GMT

Did you uncheck auto ascending in the sort tab of your listbox prpoerties.?

Re: Date not sorted correctly

Anonymous — Mon, 18 Dec 2017 20:55:49 GMT

Yes, if you don't mind. Would you put together a snippet using the values I gave in a qvw file? I will take a look at

your qvw file to see how you did it. Thanks so much for your time.

Re: Date not sorted correctly

vishsaggi — Mon, 18 Dec 2017 21:03:52 GMT

Oh sorry you mean My QVW file right?

This is what i did.

Re: Date not sorted correctly

Anonymous — Tue, 19 Dec 2017 13:37:58 GMT

Hi Vishwarath,

Thanks for putting together the qvw file. However it doesn't work the way I described it. The

November date is at the bottom of the list when I open the document. It should be at

the top (first one). Also I don't see the sort expression you explained to me in your sample.

Re: Date not sorted correctly

rubenmarin — Tue, 19 Dec 2017 14:19:41 GMT

Hi Sydney, if dates are real dates (they are right-aligned) you can disable sorting by expression and check only the numeric sort.

Or set your sorting expression to:

Min({1} yourSortDateField) // Selections won't affect sorting

Re: Date not sorted correctly

t_chetirbok — Tue, 19 Dec 2017 14:32:06 GMT

don't forget about this check box

Re: Date not sorted correctly

Anonymous — Tue, 19 Dec 2017 14:52:48 GMT

Ruben,

I unchecked "sort by expression" and use "sort by numeric" as you suggested. This seems to have fixed

my problem.

Thank you everyone who have contributed to this thread.

Re: Date not sorted correctly

Anonymous — Tue, 19 Dec 2017 15:38:34 GMT

OK. One more issue related to this. Now that the selection box is sorted properly. However my

chart isn't being rendered in the correct time order. See attached. How do I make so that "21-Nov-2017" comes

first to the left and "19-Dec-2017" being last on the right?