Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How do I display records created by hour?

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 

1 Solution

Accepted Solutions
Not applicable

Re: How do I display records created by hour?

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.

8 Replies
MVP
MVP

Re: How do I display records created by hour?

Please share a sample app.

Not applicable

Re: How do I display records created by hour?

Your approach is the way to go.

It should look something like this:

stigchel
Honored Contributor

Re: How do I display records created by hour?

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

Count([Helpdesk ID])

Not applicable

Re: How do I display records created by hour?

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

Not applicable

Re: How do I display records created by hour?

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)

Not applicable

Re: How do I display records created by hour?

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.

Not applicable

Re: How do I display records created by hour?

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

abeyphilip
Contributor II

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

Hi,

Please see the attached file.

You can check the dimension and the sort expression.

Regards,

Abey

Community Browser