Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi,
by default qlikview sort by auto ascending order. try to uncheck the load order in sort tab
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.
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?
to order on numeric value check this.
a date field is always a dual field (DD.MMM.YYYY and numeric value)
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.
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.
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
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
If you want to keep the format you have dated, you must specify in the variable of short date in this way:
Next up, cast your date format with this way:
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