Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Why don't you try this
=Interval(Frac([IM Arrival Time]), 'hh TT')
Or this
Dual(Mod(Hour([IM Arrival Time]),12)&' '&time([IM Arrival Time], 'TT'), Frac([IM Arrival Time]))
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]
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]
How about this
=Time(Floor([IM Arrival Time], 1/24), 'hh TT')
Perfect! that did it