Sort by Hour

[brf10043]

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?

[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]

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]

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 · Accepted solution]

How about this

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

[brf10043]

Perfect!  that did it