topic Dividing two Aggr() Functions in QlikView

Dividing two Aggr() Functions

chhavi376 — Mon, 25 Jul 2016 11:16:46 GMT

District Code	Product1	Value
District 1	Prod1	1
	Prod2	2
	Prod3	3
District 2	Prod1	4
	Prod2	5
	Prod3	6
	Prod4	7

I want to calculate the district wise share for every product.

the expression i am using is:

num(aggr(Sum(Value), Products,District)/aggr(Sum(Value), District) ,'#,##0.0%' )

But, this expression is not working.

Could anyone please suggest a way

Re: Dividing two Aggr() Functions

rubenmarin — Mon, 25 Jul 2016 11:53:38 GMT

Hi chhavi, I see two options:

- Use NODISTINCT:

num(aggr(Sum(Value), Product1,[District Code])/aggr(NODISTINCT Sum(Value), [District Code]) ,'#,##0.0%' )

- Do the percentage in only one aggr:

num(aggr(Sum(Value)/ Sum(TOTAL <[District Code]> Value), Product1,[District Code]) ,'#,##0.0%' )

Re: Dividing two Aggr() Functions

effinty2112 — Mon, 25 Jul 2016 12:11:22 GMT

Hi Chhavi,

i don't think you need AGGR here at all:

District Code	Product1	sum(Value)	sum(Value)/sum(TOTAL <[District Code]> Value)
District 1	Prod1	1	16.67%
	Prod2	2	33.33%
	Prod3	3	50.00%
District 2	Prod1	4	18.18%
	Prod2	5	22.73%
	Prod3	6	27.27%
	Prod4	7	31.82%

cheers

Andrew

Re: Dividing two Aggr() Functions

chhavi376 — Mon, 25 Jul 2016 12:13:02 GMT

Thanks Ruben.

It's working fine

Re: Dividing two Aggr() Functions

chhavi376 — Mon, 25 Jul 2016 12:20:05 GMT

Andrew,

I was trying to calculate the district wise share for every product in the dimension(This will remain static for me).

Also, I had to create a Pivot table, and i did not want the contents of this static dimension to move with the scroll bar, therefore i could not use it as an expression.