Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
you have to create a chart with a calculated dimension
=hour([Submit Date.Time])
and the expression Count(distinct IncidentID)
Hi,
you have to create a chart with a calculated dimension
=hour([Submit Date.Time])
and the expression Count(distinct IncidentID)
Check sample
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.
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..
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.