Calculating average YTD hours

[Anonymous]

Hi All,

I am trying to calculate the average hours spent on a project YTD.  My data contains weekly time sheets with project codes and hours charged to it on a daily basis.  In my dashboard I would like to show one number that is the average hours spent on the project across all team members.  A simple average provides a number too low, because some team members joined the project after it started or left before it ended.  I did a manual calculation in Excel where I calculated weekly averages and then summed them up (the number of people working on the project remained steady).  Could you help me recreate this calculation in QlikView?  Or do you have a suggestion on how to make the calculation even more accurate?

Sample data:

Team member    Date Worked     Hours Charged

--------------------   ------------------    ---------------------

A                         2017-05-08       6

A                         2017-05-09       8

A                         2017-05-10       5

A                         2017-05-11       5

A                         2017-05-12       6

etc.

Thank you in advance,

Jacek

[Anonymous · Accepted solution]

I will reply to myself as I figured out a way to address my challenge.

I grouped the data on a work week basis (I already had a field available in my data "Weekstart Date") and then using

Average – Which average?‌‌ post I created a formula  Sum( [Hours Charged]) / Count( distinct WeekstartDate & '|' & TeamMemberID ) which I then multiplied by the number of weeks for the project.  I compared the results to what I did in Excel and the numbers are different by a negligible amount, so i am sticking to this solution.  Thank you HIC for your blog post !