How to take Average across data in a Column

Wed, 14 Oct 2015 13:19:10 GMT

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,

Re: How to take Average across data in a Column

hic — Wed, 14 Oct 2015 13:22:36 GMT

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

Sum(Duration)

Sum(Maximum)

Sum(Duration) / Sum(Maximum)

HIC

Re: How to take Average across data in a Column

Wed, 14 Oct 2015 13:42:32 GMT

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,

topic How to take Average across data in a Column in QlikView

How to take Average across data in a Column

Re: How to take Average across data in a Column

Re: How to take Average across data in a Column