Average number of records by hour of the day over a period

[Fabrizio Zaffaroni]

Good Afternoon,

I would like to show in a bar char the average number of generated records by hour of the day over the period from the first date to the last date of my dataset. My dataset is something like this:

20/10/2020 10.30.23

20/10/2020 13.32.21

21/10/2020 10.05.23

I have on the X Axis of the chart: BinByDateTime([Date],"Hour",0).

I would like to see two columns:

10 with value1

13 with value0,5

I'm not sure about how to set the Y Axis custome espression in order to obtain this result, I tried something like this, but it's not working:

Avg(Count([Date]) over (All(Date)))

It gives:

10 with value 2

13 with value 1

Thanks for the help

[Fabian Duerr]

Please get your datetime format right! It helps the people who try to help you.

 

10/20/2020  10:30:23 AM

 

10/20/2020  1:32:21 PM

 

10/21/2020  10:05:23 AM

 

Please clarify why you want this output...

 

I only get this result if I divide the counts per hour by the number of unique days in the data set. Is this what you want If yes, create a new column 'unique days' the gives the number of uniqe days in your data set:

 

UniqueCount(Day([Date]))

 

Then use any aggregation for this column in your bar chart. I use Max here.

 

X Axis: BinByDateTime([Date],"hour",0)

 

Y Axis: count() / Max([unique Days])