Calculating average YTD hours

Anonymous — Fri, 22 Sep 2017 20:06:59 GMT

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

Re: Calculating average YTD hours

Anonymous — Wed, 04 Oct 2017 19:55:00 GMT

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 !

topic Re: Calculating average YTD hours in QlikView

Calculating average YTD hours

Re: Calculating average YTD hours