I built a report that loads a database table of logged event data. The database table includes a datestamp field containing the date and time of the event. I wanted my report to have a chart showing the duration in hours, minutes, and seconds between the events shown on each row of the chart. That would allow me to make selections on types of events or the source of the event and the time duration between listed events would be useful.
I created a straight-table chart with a dimension of a field called EVTLOG_ID. That single field is a unique identifier that I could sort by descending value and guarantee that the rows in my chart are in chronological order with the most recent events at the top of the chart. Then I added an expression that simply said =EVTLOG_DATE to show the timestamp field from the table. Then I added another expression like this =time(interval([EVTLOG_DATE]) - below([EVTLOG_DATE])),'h:mm:ss') to show the interval between the timestamp field and the timestamp field on the lower line in the chart. The outer function, time, makes the format show as hours, minutes, and seconds without the unneeded "AM" label.
The chart works great – figuring out the elapsed time between timestamps is a tiresome chore to do in your head.
★ ★ ★
6 comments:
Hello, Tim.
I think it would be more easier :) You can use an interval() function with above/below chart functions. For example,
interval([XDT] - below([XDT]))
best regards,
Anatoly Pyatygo
qvrus.blogspot.com
That's a great suggestion. I'm going to change the posting to use that idea. Thanks!
Tim,
Do you recall the original approach you took for this posting?
Having the two approaches in contrast might help some folks find the better approach when they search for examples doing the original.
Most of my blog postings are things you cannot find in the manual or unusual solutions that I've never seen anyone else use.
But, for timestamps and time arithmetic, you can find many postings and ideas on QlikCommunity.
Hi,
What if we want to display this time in the following format:
(2 days 20 hrs 22 mins 32 seconds)
instead of
(02 20:22:32)
Do you have any suggestion?
You would want to try several alternatives but I think you would need to concatenate several text functions together. For example, if your time expression is XXX then something like this could work:
=floor(upper(time(XXX,'hh'))/24)&' days '&mod(upper(time(XXX,'hh')),24)&' hrs '&time(XXX,'mm')&' mins '&time(XXX,'ss')&' seconds'
I think I would put that into a variable defined to accept a parameter or argument. Then put the time expression into the argument. I have a blog posting about that-- search on "qlikview maven Variable That Acts Like a User-Defined Function".
Post a Comment