Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
As long as the dimension is employee, a simple avg(EndTime-StartTime) should work.
Regards.
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.
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.
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.
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.