How to Calculate Hourly Average

#1

Hi ,
Good Day,

We have a requirement to calculate Hour Of Day Average, for example, here with I have attached sample data.

we have a below data set, Hour Of Day (01:00, 02:00, 03:00, 04:00), Date/Day Number and Total Visits.
Based on that I’m trying to calculate “Avg Hourly Visits”, But can’t figure out how to use formula for it .

Avg Hour Visits = (total Visits / Count of Days/Date ) and group By Hours Of Day.

Sample data attached in the screenshot.

Data
Data.png420×563 6.58 KB

Thank you in Advance.
Jatin.

#2

I do something similar to this you should be able to take the approach I use and make it work.

I have to calculate the percentage of hours charged by an employee to a certain project compare to their total time that pay period but not include Holiday/PTO/other leave. Not only that but if report is ran for a 3 month period and the person only charged to this project one week I cannot count any hours from other weeks where they did not charge to this project.

OK my Solution to all that:
in the metric set Designer I have the Fx “$DirectHoursToProject$/$TotalHoursWithOutLeave$”, but that is not where it stops, just that doesnt work. you have to go in and edit the Measure. the Aggegator I have is Average. unchecked use details and grand total. the calculation axis is Columns.

I had to fiddle with all that a lot before i landed on the right combination that used the correct values.

I think version 7 has a sum by grouping that you might need to use possibly.

#3

Thank you James,

Let I will try it and let you know.

we have Version 7, So hope so it will work.

Wish you a Merry Christmas…!!
have a Great Day.

Thanks.
Jatin.

#4

Hi James,
Good Morning…!!

I tried but it is not working in my case, as I required every hour Average Visits based on number of days total Visits for particular hour. As I stated in above table data.

I also tried with Avg Formula, but it gives me all over average but not hourly. I Required above data table field “Avg Hourly Visits” result.

Thanks and Appreciate.
Jatin.

#5

Is this in a database. You might have to use some window functions is the sql to get what you want. Or part of what you want like the sum by that you don’t show but use in the formula.

#6

Hi James,

Now it is resolved through AVG formula, now it is working fine.

Thanks and have a happy holidays…!!

Jatin.