topic Re: How do I display records created by hour? in QlikView

How do I display records created by hour?

Tue, 17 Sep 2013 12:05:35 GMT

Hi,

Have a table with helpdesk records created over time. The field "CreatedDateTime" displays when the records were created. I have loaded a new field called "RoundedCreatedTime" that only displays the actual time and also rounds the time to the closest hour.

I would like to create a line chart that shows a trend of when most of the helpdesk records are created by hour, during a day. If I create a chart with dimension "RoundedCreatedTime", and expression Count(Helpdesk ID), the chart is not displayed correctly.

I have pretty much run out of ideas. Can someone put some light on this?

Thanks,

Mikael

Re: How do I display records created by hour?

tresB — Tue, 17 Sep 2013 12:19:00 GMT

Please share a sample app.

Re: How do I display records created by hour?

Tue, 17 Sep 2013 12:21:57 GMT

Your approach is the way to go.

It should look something like this:

Re: How do I display records created by hour?

stigchel — Tue, 17 Sep 2013 12:27:03 GMT

Have you taken in account that field names with spaces in them need to be enclosed in brackets?

Count([Helpdesk ID])

Re: How do I display records created by hour?

Wed, 18 Sep 2013 07:48:15 GMT

Hi again,

Thanks for the input. The field was actually called IncidentID so the brackets are not necessary.

Erik, looking at your file it looks like we have the same settings in the chart object. I enclose the application that I am working with. Looking in the chart you can see that:

1. The sorting is not correct. The timestamps are not displayed in a chronological order

2. The same timestamp is displayed multiple times in the chart.

Any suggestions?

/Mikael

Re: How do I display records created by hour?

Wed, 18 Sep 2013 07:52:53 GMT

Hi, I couldn't open your application.

It sounds like maybe your rounding doesn't work, the time fields might be displayed similarly but they might still be different for like a millionth of a second.

Try the following:

Date(Floor(Timefield, (1/24) ), 'hh:mm')

Date(Floor(Timefield, 0.041666666666666), 'hh:mm') as IncidentHour.

or if you have a timestamp rather than just a time field

Time(Floor(TIME(FRAC(TIMESTAMPFIELD), 'hh:mm:ss'), (1/24)) 'hh:mm')as IncidentHour

======================Edit================

I was able to open your file, judging on your field names this should work:

hour(ActualDateTime)

Re: How do I display records created by hour?

Wed, 18 Sep 2013 08:03:22 GMT

If you change the dimension to Hour(CreatedRoundTime) you get the correct result.

The CreatedRoundTime are indeed different. (Have a look at Daniels reply)

Just add a listbox of CreatedRoundTime to your tab and you'll see many different times.

Re: How do I display records created by hour?

Wed, 18 Sep 2013 08:07:17 GMT

Though he should use the original field, it doesn't make sense to keep a CreatedRoundTime field that isn't actually rounded..=)

Re: Re: How do I display records created by hour?

abeyphilip — Wed, 18 Sep 2013 08:14:31 GMT

Hi,

Please see the attached file.

You can check the dimension and the sort expression.

Regards,

Abey