How do I display records created by hour?

[#1]

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 

[tresesco]

Please share a sample app.

[#3]

Your approach is the way to go.

It should look something like this:

[stigchel]

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

Count([Helpdesk ID])

[#5]

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

[#6]

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)

[#7 · Accepted solution]

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]

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

[abeyphilip]

Hi,

Please see the attached file.

You can check the dimension and the sort expression.

Regards,

Abey