topic Inner AGGR problem in QlikView

Inner AGGR problem

Fri, 14 Jul 2017 11:54:27 GMT

Hi everybody,

I've got problem with aggr() function. I need to get average value (to use it on a chart), where some data had to be excluded. I,ve got some data (please see the attached .xls) with fields:

Date, MAIN_NR, SUB_NR, RATIO, VALUE

I need to get average for (sum(RATIO)/sum(VALUE)) where data for SUB_NR with minimum Date for every MAIN_R are excluded. I've tried to use the expression:

= avg(aggr(
(
sum({<Date ={'>$(=Date(min(aggr(min(Date), MAIN_NR))))'}>}RATIO)
/
sum({<Date ={'>$(=Date(min(aggr(min(Date), MAIN_NR))))'}>}VALUE)
)
,MAIN_NR)
)

Unfotunately aggr() function inside the set analysis always gives the minuimum date for all the selected values (not for specified MAIN_NR).

Refering to the attached .xls:

for MAIN_NR=49 SUB_NR=83436027 should be excluded and result should be 0,917,

for MAIN_NR=11 SUB_NR=83647867 should be excluded and result should be 0,416,

for MAIN_NR=35 SUB_NR=83309998 should be excluded and result should be 1,57,

Average should be 0,968

It has to work with different selections, so it has to refer to the minimum date from the selections, not the minimum date in all the data.

Appreciate if any expert could suggest the solution. Thanks

Cezary

Re: Inner AGGR problem

sunny_talwar — Fri, 14 Jul 2017 14:31:27 GMT

I got very close to your numbers (11 isn't matching)... could be a miscalculation on your end?

=Avg(Aggr((

Sum(Aggr(If(Date > Min(TOTAL <MAIN_NR> Aggr(Date, MAIN_NR, Date)), RATIO), MAIN_NR, Date, RATIO))

Sum(Aggr(If(Date > Min(TOTAL <MAIN_NR> Aggr(Date, MAIN_NR, Date)), VALUE), MAIN_NR, Date, VALUE))), MAIN_NR))

Re: Inner AGGR problem

krishnacbe — Fri, 14 Jul 2017 14:32:36 GMT

Hi,

Try below expression. This expression give same output you shared

avg(aggr(
(
sum({<Date ={'>$(=Date(min(aggr(min(Date), MAIN_NR))))'}>}RATIO)
/
sum({<Date ={'>$(=Date(min(aggr(min(Date), MAIN_NR))))'}>}VALUE)
)
,SUB_NR)
)

Re: Inner AGGR problem

effinty2112 — Fri, 14 Jul 2017 20:15:19 GMT

Hi Cezary,

Try:

MAIN_NR	Expr
11	0.416
35	1.5702941176471
49	0.91683544303798

Where Expr =

(Sum(RATIO)-Sum({$<SUB_NR = {$(=Concat(Aggr(FirstSortedValue(SUB_NR,Aggr(Min(Date),MAIN_NR)), MAIN_NR),','))}>}RATIO))

(Sum(VALUE)-Sum({$<SUB_NR = {$(=Concat(Aggr(FirstSortedValue(SUB_NR,Aggr(Min(Date),MAIN_NR)), MAIN_NR),','))}>}VALUE))

For the data given this is equivalent to

(Sum(RATIO)-Sum({$<SUB_NR = {83309998,83436027,83647867}>}RATIO))

(Sum(VALUE)-Sum({$<SUB_NR = {83309998,83436027,83647867}>}VALUE))

The difficulty with using set analysis in a chart is that the set is not evaluated row by row but once only for the whole chart and this can be problematic. Here the set of SUB_NR are those elements to be excluded from each row. The fact that only one of these elements affects each row's calculation does not affect the result.

Cheers

Andrew

Re: Inner AGGR problem

Mon, 17 Jul 2017 06:39:17 GMT

Hi,

It's close but your expression gives results:

for MAIN_NR=49: 0,883

for MAIN_NR=11: 0,419

for MAIN_NR=35: 1,567

and avg is 0,965

thanks anyway

Cezary