5 Replies Latest reply: Sep 30, 2010 6:51 PM by Karl Pover

# Calculating Average Times in a Chart Table

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.

• ###### Calculating Average Times in a Chart Table

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

Regards.

• ###### Calculating Average Times in a Chart Table

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.

• ###### Calculating Average Times in a Chart Table

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.

• ###### Calculating Average Times in a Chart Table

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.

• ###### Calculating Average Times in a Chart Table

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.