topic Re: Sort by Hour in QlikView

Sort by Hour

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

Team,

I am creating a bar chart that tracks the number of incidents by hour of the day. I am using the following code to convert the IM Arrival Time into an hour with AM/PM for my dimension . I'm thinking that there might be a better way to do this, but I've not found it yet.

=Mod(Hour([IM Arrival Time]),12)&' '&time([IM Arrival Time], 'TT')

Now that I have this working, I can't seem to get the chart to sort by hour (Midnight to 11PM) Here's what I currently have. Any suggestions?

Re: Sort by Hour

sunny_talwar — Tue, 30 Oct 2018 19:50:03 GMT

Why don't you try this

=Interval(Frac([IM Arrival Time]), 'hh TT')

Re: Sort by Hour

sunny_talwar — Tue, 30 Oct 2018 19:51:07 GMT

Or this

Dual(Mod(Hour([IM Arrival Time]),12)&' '&time([IM Arrival Time], 'TT'), Frac([IM Arrival Time]))

Re: Sort by Hour

brf10043 — Tue, 30 Oct 2018 19:57:05 GMT

Thanks Sunny, but neither of those ideas work. I end up with multiple bars for a given hour. Not sure if this may help, but here is the code in the load script that creates the Arrival_Time Column

time(frac(ARRIVAL_TIME/ 86400 + 25569),'hh:mm:ss') as [IM Arrival Time]

Re: Sort by Hour

brf10043 — Tue, 30 Oct 2018 20:21:20 GMT

Also, taking a closer look at the results using =Mod(Hour([IM Arrival Time]),12)&' '&time([IM Arrival Time], 'TT') I don't think that it's working properly. As 12 noon is showing up as 0 PM.

I wonder if there is something I should be doing in the load script to convert the Arrival_Time column to Arrival hours formatted as 12AM through 11 PM.

The code that creates the Arrival Time column is below. It takes a Remedy date (Date in seconds) and converts it into a time. If I could convert it directly to an hour am/pm in the load script it might be better than trying to convert it in the dimension.

time(frac(ARRIVAL_TIME/ 86400 + 25569),'hh:mm:ss') as [IM Arrival Time]

Re: Sort by Hour

sunny_talwar — Tue, 30 Oct 2018 22:27:08 GMT

How about this

=Time(Floor([IM Arrival Time], 1/24), 'hh TT')

Re: Sort by Hour

brf10043 — Wed, 31 Oct 2018 12:59:47 GMT

Perfect! that did it