Calculating Average Times in a Chart Table

[#1]

Hi Gurus:

Please bail me out once again. I have a table:

Employee	StartTime	EndTime
Bubba	8/15/10 08:00	8/16/10 10:20
Bubba	8/18/10 12:00	8/20/10 12:00
Hazel	8/08/10 09:30	8/08/10 10:15

We want to get the average, by person of time to complete each task. So, Bubba's average is (26:20 + 48:00) / 2 = 37:10. Hazel is simply 00:45.

Resulting table should be:

Employee	Average (in hours)
Bubba	37:10
Hazel	00:45

My Pivot Table current expression is:

avg(aggr(sum(EndTime - StartTime), Employee)), which is obviously not working. Any suggestions are greatly appreciated.

[pover]

As long as the dimension is employee, a simple avg(EndTime-StartTime) should work.

Regards.

[#3]

Hi Karl,

I originally tried this, I'm getting 201:15 as a result from 441:37 and 81:04 (should be 261:41). I have no idea how QlikView calculated that number, it does not make any sense.

[pover]

I imported the table above from Excel and the avg() funtion seemed to work along with the function sum(EndTime-StartTime)/count(Employee)*24*60. Does the table in your data source really have the structure you posted?

Regards.

[#5]

No, it does not. . . I tried to simplify to take out the complexities of the table. Let me mess with this a little more and I'll post my findings/questions. I'll make sure to include all of the table structures. Thanks for your help so far.

[pover]

Ok, sounds good. My last suggestion by assuming what the table structure is like, I would try something like this:

avg(aggr(sum(EndTime - StartTime), CaseID))

Good luck.