topic Re: How to calculate average hours per week in QlikView

How to calculate average hours per week

Tue, 18 Jun 2013 09:53:42 GMT

All worked hours are registered in our database. Hours are registered per week and per project.

I have created a pivot table that shows worked hours per week, and the total of worked hours for each project. So far so good.

Now I want to add an extra column that shows the average number of hours worked per week for each project.

The average should be taken of the total number of weeks registered (so regardless of the project!) If no hours were registered in a week than this week should not be used in the calculation.

See the Excel example for clarification.

Hope someone can help!

Re: How to calculate average hours per week

Gysbert_Wassenaar — Tue, 18 Jun 2013 17:01:43 GMT

See attached example.

Re: How to calculate average hours per week

Wed, 19 Jun 2013 06:10:17 GMT

This is almost what I would like to see: I knew that the formula shouldn't be to difficult, but I couldn'd figure it out .

It would be nice if only the avg column at the right side of the table would be shown (see attachment, so no avg column for each week but only as a total), but I think this is not possible, is it?

Re: How to calculate average hours per week

Gysbert_Wassenaar — Wed, 19 Jun 2013 08:12:19 GMT

No, that's not possible. You could get such a column on the left by adding a calculated dimension. See attached example.

Edit: updated the example. QV is case sensitive so Week is not the same as WEEK. Use the exact field names and it'll work.

Re: How to calculate average hours per week

Wed, 19 Jun 2013 09:15:28 GMT

It seems like it is what I need, but.. when I apply my 'real' data, no results are shown.

See attachment for Excel sheet and 'my' QV file. What am I doing wrong?