Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
brf10043
Creator
Creator

Sort by Hour

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?

1 Solution

Accepted Solutions
sunny_talwar

How about this

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

View solution in original post

6 Replies
sunny_talwar

Why don't you try this

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

sunny_talwar

Or this

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

brf10043
Creator
Creator
Author

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]

brf10043
Creator
Creator
Author

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]

sunny_talwar

How about this

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

brf10043
Creator
Creator
Author

Perfect!  that did it