5 Replies Latest reply on Sep 28, 2016 10:33 AM by Nitish Pamarty

I imagine this is easy, but I cannot figure it out. I have a list of event dates (including time). I would like to present a bar chart of average number of events by hour, by day, by day of the week, over the period containing the data.

So the "Ave By Hour" chart would should the average number of events for each hour that occurred each day

the "ave by weekday"  would should the average number of events by weekday over the period

I think you get the idea.

• 1. Re: Row averages, help please

Have you tried calculating the average using a formula such as SUM([Number of Records])/COUNTD(DATE([Event Date]))?

I'm not 100% sure about the rules behind your average calcuation but as long as you're able to get the correct numerator and demoninator you should be good.

• 2. Re: Row averages, help please

Hi Allan,

You might want to use countd of DATETRUNC to get the avg. I've attached the solution.

Thanks!

• 3. Re: Row averages, help please

Very nice. many thanks.

So to clarify, the averages shown essentially say "This many events, on average, happened during this hour (or during this day)?"

• 4. Re: Row averages, help please

Unfortunately, this isn't working. Take 3:00 am. There are two entries, but the bar graph reports 1.00

Also, the period runs july 8 through sept 29. That is like 80 some odd days. The average should be 2/80 for 3:00am.

• 5. Re: Row averages, help please

Sorry for the delay in response.

Can you replace the denominator of Hour Avg to:

max(DATEDIFF('day', { FIXED [Number of Records] : MIN([Event Date])} , {FIXED [Number of Records]: MAX([Event Date])}))

This would make denominator to be 83.

1 of 1 people found this helpful