topic Aggr() function not working in expression the same as calculated dimension in QlikView

Aggr() function not working in expression the same as calculated dimension

jdf — Wed, 14 Sep 2011 15:48:29 GMT

Good Day!

I am trying to use an aggr function in an expression as a denominator to compare a branch’s margin to its division’s sales. When I use the aggr() function in a calculated dimension I get the values I want, and they populate for every row (Calc Dim Aggr in table below). However if I use it in an expression the aggr only works for one branch within the division (Expressoin Aggr in table below). The formula is exactly the same in the expression as it is in the dimension.

Any insight or workaround would be appreciated.

Division Description	Branch Description	Calc Dim (Aggr)	Margin	Expression (Aggr)	Extended Price
Division 1	Branch 1	$178,130,550.42	3.31%		$833,041.18
Division 1	Branch 2	$178,130,550.42	16.92%		$14,450,580.82
Division 1	Branch 3	$178,130,550.42	56.26%	$178,130,550.42	$162,846,928.42
Division 2	Branch 4	$2,350,939.84	83.10%		$2,346.06
Division 2	Branch 5	$2,350,939.84	45.48%	$2,350,939.84	$2,348,593.79
Division 3	Branch 6	$67,639,081.70	41.05%		$923,144.17
Division 3	Branch 7	$67,639,081.70	-928.05%	$67,639,081.70	$5,204,648.75
Division 3	Branch 8	$67,639,081.70	-52.21%		$61,511,288.78

Thanks,

~jake~

Aggr() function not working in expression the same as calculated dimension

jdf — Wed, 14 Sep 2011 15:59:06 GMT

I figured it out on my own!

Instead of using aggr() i just used sum(total <[Division Description]> [Sum Field]).

Aggr() function not working in expression the same as calculated dimension

IAMDV — Wed, 14 Sep 2011 16:01:29 GMT

Hi Jake,

Please can you post the expression for the field - Expression (Aggr)? It will be easy to look at the expression. I am assuming that you are using...

Aggr(Sum ([Extended Price]), [Division Description])

Is this right?

Cheers - DV

Aggr() function not working in expression the same as calculated dimension

jdf — Wed, 14 Sep 2011 16:08:31 GMT

Hey DV,

That is correct.

~jake~

Aggr() function not working in expression the same as calculated dimension

IAMDV — Wed, 14 Sep 2011 16:11:34 GMT

Cool. I am glad that you have got it working.

Cheers - DV

Aggr() function not working in expression the same as calculated dimension

jdf — Wed, 14 Sep 2011 16:12:51 GMT

Me Too