Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
darren_dixon
Contributor III
Contributor III

Count by hour

Hi,

I'm looking to create to show how many incidents were raised per hour.

sample data

IncidentIDSubmit Date.Time
10112308/10/2013 09:31:46
10112408/10/2013 09:45:12
10112508/10/2013 11:23:01
10112608/10/2013 12:32:54
10112708/10/2013 13:15:45
10112808/10/2013 15:20:59
10112908/10/2013 16:22:51
10113008/10/2013 17:28:54

I'd like the chart to have hours as the X axis and a count of incidents as the Y axis.

Thanks,

Darren

1 Solution

Accepted Solutions
bbi_mba_76
Partner - Specialist
Partner - Specialist

Hi,

you have to create a chart with a calculated dimension

=hour([Submit Date.Time])

and the expression Count(distinct IncidentID)

View solution in original post

5 Replies
bbi_mba_76
Partner - Specialist
Partner - Specialist

Hi,

you have to create a chart with a calculated dimension

=hour([Submit Date.Time])

and the expression Count(distinct IncidentID)

MK_QSL
MVP
MVP

Check sample

Gysbert_Wassenaar

If you have a lot of records it's best to split the datetime into a date and time field and also create an hour field:

Load

   IncidentID

   floor([Submit Date.Time]) as [Submit Date],

   frac([Submit Date.Time]) as [Submit Time],

   hour([Submit Date.Time]) as [Submit Hour],

   ...other fields...

From ...source...;

You can then use the new Submit Hour field as dimension in your chart.


talk is cheap, supply exceeds demand
kim_rormark
Partner - Contributor III
Partner - Contributor III

As G says, if you have a lot of records it will require a lot of calculation during selections if you do the calculation in the object, and it will be a better practice to create the time-field during load.

Not so important if there are just a small count of records, though..

kim_rormark
Partner - Contributor III
Partner - Contributor III

Although this is correct, I would really recommend doing the calculation part in the load script.

From G in the previous post:

Load

   IncidentID

   floor([Submit Date.Time]) as [Submit Date],

   frac([Submit Date.Time]) as [Submit Time],

   hour([Submit Date.Time]) as [Submit Hour],

   ...other fields...

From ...source...;

Then, use the [Submit Hour] as the dimension and Count(IncidentID) as the expression. I would not recommend using the DISTINCT function in the count, unless you know that the same IncidentID could appear more than once. This is also something that could potentionally give you a performance "boost".

Remember, this is especially important if there are a large amount of records - but I will always try to do best practice before easy.