topic Re: Count by hour in QlikView

Count by hour

darren_dixon — Tue, 08 Oct 2013 12:21:07 GMT

Hi,

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

sample data

IncidentID	Submit Date.Time
101123	08/10/2013 09:31:46
101124	08/10/2013 09:45:12
101125	08/10/2013 11:23:01
101126	08/10/2013 12:32:54
101127	08/10/2013 13:15:45
101128	08/10/2013 15:20:59
101129	08/10/2013 16:22:51
101130	08/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

Re: Count by hour

bbi_mba_76 — Tue, 08 Oct 2013 12:32:38 GMT

Hi,

you have to create a chart with a calculated dimension

=hour([Submit Date.Time])

and the expression Count(distinct IncidentID)

Re: Count by hour

MK_QSL — Tue, 08 Oct 2013 12:35:18 GMT

Check sample

Re: Count by hour

Gysbert_Wassenaar — Tue, 08 Oct 2013 12:47:52 GMT

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.

Re: Count by hour

kim_rormark — Tue, 08 Oct 2013 12:51:35 GMT

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..

Re: Count by hour

kim_rormark — Wed, 09 Oct 2013 13:35:42 GMT

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.