topic Calculating Average Times in a Chart Table in QlikView

Calculating Average Times in a Chart Table

Thu, 30 Sep 2010 18:56:05 GMT

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

pover — Thu, 30 Sep 2010 19:26:38 GMT

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

Regards.

Calculating Average Times in a Chart Table

Thu, 30 Sep 2010 20:28:13 GMT

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

pover — Thu, 30 Sep 2010 21:23:26 GMT

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

Thu, 30 Sep 2010 22:42:36 GMT

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

pover — Thu, 30 Sep 2010 22:51:06 GMT

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.