How to take Average across data in a Column

[#1]

Hi,

I have following data:

I created this table in QlikView. Duration and Maximum columns are in "hh:mm" format.

"Utilization" column = "Duration" divided by "Maximum" in percentage for each row.

Table1:

Key	Date	Duration	Maximum	Utilization
1	06/26/2013	0:26	0:36	72%
1	06/27/2013	2:56	10:26	28%
1	06/28/2013	2:30	9:39	26%
2	06/26/2013	0:26	0:36	72%
2	06/27/2013	2:55	10:26	28%
2	06/28/2013	1:40	9:39	17%
3	06/26/2013	0:26	0:36	72%
3	06/27/2013	1:40	10:26	16%
3	06/28/2013	2:05	9:39	22%
4	06/26/2013	0:25	0:36	69%
4	06/27/2013	1:40	10:26	16%
4	06/28/2013	1:40	9:39	17%

I want to take the sum of column "Duration" for each "Key" across three dates and then divide the result by sum of all values in the "Maximum" column for each key across all dates.

The result must look like this:

Table2:

Key	DurationSum	MaximumSum	UtilizationSum
1	5:52	20:41	28.36%
2	5:01	20:41	24.25%
3	4:11	20:41	20.23%
4	3:45	20:41	18.13%

where "DurationSum" column is sum of first three rows from "Table1" for "Key 1" and "MaximumSum" is sum of first three rows for "Key 1" from "Table1". UtilizationSum is DurationSum divided by MaximumSum

How do I get Table2

I am surprised that I cannot get QlikView to do this either as an Expression in the Chart or in the Table. I tried average. I even tried creating a variable in the Load script.

Seems simple. Can someone please guide?

Thanks,

[hic · Accepted solution]

Make a chart with Key as dimension, and the following three expressions:

Sum(Duration)

Sum(Maximum)

Sum(Duration) / Sum(Maximum)

HIC

[#3]

Hi Henric,

Thanks for the quick response. I have used "Key" and "Date" as Dimensions

"Duration", "Maximum", and "Utilization" are Expressions.

I am currently using Duration = Interval(Sum(EndDate - StartDate),'hh:mm'). Maximum value is derived using a complicated expression. I did not use Sum(Maximum).

It worked.

Thanks,