Aggregate over an expression

Fri, 27 May 2016 18:16:35 GMT

I'm new in qlik and I'm trying to accomplish the following request with no luck, I hope someone can give some ideas:

My issue is having a % value calculated based 2 columns by Year and Week:

Year Week Value 1 Value 2 %

year week sum(x) sum(y) [Value 1] / [Value 2]

The results having year and week in the chart works fine, but now I would like to create a second chart having only Year and % where the percentage is not really the natural aggregation of this value, i would like to have an average of the results.

having this data:

Year	week_id	KeyId	Total Misc Sales	Total Dept Sales	%
Year FY16	201648	4	$19,583.85	$2,209,132.25	0.89%
Year FY16	201649	4	$0.00	$5,157,718.87	0.00%
Year FY16	201650	4	$286.20	$4,360,959.95	0.01%
Year FY16	201651	4	$8.50	$3,736,121.04	0.00%
Year FY16	201652	4	$7.99	$1,308,088.04	0.00%
			$19,886.54	$3,354,404.03	0.12%

I would like to get

Year	KeyId	%
Year FY16	4	0.18%

Where 0.18% is the Average of % values divided of number of weeks

But Qlik will give me:

Year	KeyId	%
Year FY16	4	0.12%

Any ideas will help

Thanks

Darlene

Re: Aggregate over an expression

sunny_talwar — Fri, 27 May 2016 18:48:29 GMT

May be try like this:

Avg(Aggr(Sum(x), Year, week_id))/Avg(Aggr(Sum(y), Year, week_id))

topic Aggregate over an expression in QlikView

Aggregate over an expression

Re: Aggregate over an expression