Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Sorting by time


I have a Straight table where one of many columns is Time:

10:01 am

10:02 am

10:03 am

... and so on

I want to be able to sort on time by clicking the Time column => but when I do, it sorts in a rather strange way, with no logic (at least it seems) in the sort.

When I check the sort properties I find the choices to sort by by "text", "number/value", "frequency," and "Load order"...

Could this have something to do with Time not being text or a number? I have tried sorting by both of them, but it does'nt sortthe way I want it to... Can anyone give some hints/directions for how I can handle this types of sorts?

Tags (2)
2 Replies

Sorting by time


you should check that you have read the Time values in as recognized time value. Either assure that the default format matches this time format code or foce QV to read the value as time value while loading, maybe like



time#(TIMEFIELD,'hh:mm tt') as Time,



Please look into the Help for time#() function and the format codes. If you want to format the time values differently, please look also into time() function.

The sorting should not be a problem with recognized time values with its numerical representation then.




Re: Sorting by time

You may be loading from a timestamp, and doing something like this:

time(MyTimestamp) as MyTime

It looks perfectly logical, but it also doesn't work, as time() is just a formatting function.  The underlying value is STILL a timestamp, and contains hidden date information.  If you sort numerically, your data would then be in "random" order because the sort is including the hidden date.  If that's what's going on, you'd want to load it more like this:

time(frac(MyTimestamp)) as MyTime

The frac() function will remove the date portion, leaving you with only a time, or more technically just the numeric equivalent of a time.  The time() function then formats that number as an actual time field.  A numeric sort will then work properly.

Community Browser