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.
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.