2 Replies Latest reply on Oct 25, 2018 5:06 AM by leonardo.zanobetti

# Average of occurrences by hour of the day

Hello!

I have recently started to learn Tableau. I have not been able to find an answer to this question yet, thus I am posting it here.

I have a dataset about crimes in Chicago from 1st January 2018 until mid-October. The dataset includes an ID for every crime committed, a description of the type of crime, and the date and the hour when it happened.

I would like to make a bar chart where each bar represents the average number of crimes committed at that hour throughout the period of my dataset. For instance, the first bar should present the average number of crimes committed between midnight and 00:59. I guess I need to make a calculated field, but any help on this would be great.

For now, I only managed to make a bar chart that shows the total number of crimes committed throughout the day for the period included in my dataset (see picture).

I attached a copy of the workbook. I am working on a Mac 10.13.6 with Tableau 2018.2.2.

Any help would be highly appreciated!

Thank you very much.

• ###### 1. Re: Average of occurrences by hour of the day

hi Leonardo,

So one way to do this is like so....

First I created a Day level of your Date Time (so just truncated down to day)

[Date Day]

DATE([Date])

I can then use this field to work out any average for a day like so

[Average Crimes Per Day]

COUNTD([ID])

/

COUNTD([Date Day])

As this is an aggregate calculation it will just work for whatever level we have in our Viz, and for any filters.

So I bring in your datetime, and select the hour (part) level...and that's it

I've also added an extra viz 'Calc Check' so you can see what the calculation is doing. Looks like you don't want to be out in Chicago at midnight on New Years Eve!!!

Hope that is what you were after and makes sense

1 of 1 people found this helpful
• ###### 2. Re: Average of occurrences by hour of the day

Thank you very much!