Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Inner AGGR problem

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

4 Replies

Re: Inner AGGR problem

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))

Partner
Partner

Re: Inner AGGR problem

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)

effinty2112
Honored Contributor

Re: Inner AGGR problem

Hi Cezary,

Try:

MAIN_NR Expr
110.416
351.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

Not applicable

Re: Inner AGGR problem

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