How do I show the daily average of a row count by hour of day

[Doug Zedler 2]

I have a bar chart displaying incident data (one record per incident). Y axis is row count. X axis is the hour that the incident occurred. Each bar shows the number of incidents throughout the data set that occurred at that hour of day. I want it to show the average number of incidents that occurred daily at that hour of day. So, for instance, on average we have had 24 incidents occur each day during the 1 PM hour of the day. Thanks for your help!

[Stina Larsson]

Hi!

I do not know how the format of your data looks like, however an idea would be to create a calculated column where you count number of rows for each hour and divide it with the unique count of the date:

Count() OVER ([Hour]) / UniqueCount([Date])

Hour and Date are the 2 column names I have in my small sample file when testing.

Then you can for example choose to use side-by-side bars, using the same scale, to show actual date values and the overall average for the hour over a period of time.

I have attached a small sample .dxp.

If this is not applicable to your scenario, then I recommend that you open a case with us and we can look deeper into your specific scenario!

Kind regards

Stina Larsson

[Doug Zedler 2]

Stina,

 

Thank you for taking the time to reply!  I'm not able to open your dxp because we're still on 7.8.  (I'm waiting for our purchasing department to get our TIBCO maintenance contract up to date before performing an upgrade).  However, I found an answer.  On the Y axis, I used this expression:

 

Count() / (DateDiff("day",Min([Alarm Date]),Max([Alarm Date])) + 1)

 

This divides the row count by the number of days in the dataset represented by the visualization.  Then, the visualization splits up the row count by the hour of day.

 

Thanks,

 

Doug