topic Re: Problem Avg and AGGR in QlikView

Problem Avg and AGGR

Anonymous — Thu, 17 Oct 2013 11:11:45 GMT

hi, I have a big problem to use of AVG + AGGR, I'm trying to make an average between these two values, for example:

90.3 and 136.4, but for me values > 100 are 100

my formula is:

IF(

AVG(

AGGR/count(A)\count(B), D1,D2,D3)>100,

100, AVG(AGGR/count(A)\count(B), D1,D2,D3)

)

the result is 100 that (in reality it would be 113.35) avg between (90.3 and 136.4)

my correct result would be 95.15 avg (90.3 and 100)

Do you have any solution about?

thanks

swuehl — Thu, 17 Oct 2013 11:17:19 GMT

I can't really understand your expression, what does this mean: AGGR/count(A)\count(B), D1,D2,D3

In principle, something like

=avg( if( FIELD > 100,100,FIELD ))

shall work.

or if you need advanced aggregation, maybe

=avg( aggr( if(FIELD > 100,100,FIELD), DIM1, DIM2, DIM3))

christian77 — Thu, 17 Oct 2013 11:23:51 GMT

Hi:

1st Use sets.

2nd Avg does not work fine with null values.

3rd Do the avg first and enclose it on aggr. If your aggr is for a table and you see only one value go like:

aggr( NODISTINCT avg (expression) , DIM1, DIM2, DIM3)

4th then add your if like

if( aggr( NODISTINCT avg (expression) , DIM1, DIM2, DIM3) > 100, 100,

aggr( NODISTINCT avg (expression) , DIM1, DIM2, DIM3))

luck.

Anonymous — Thu, 17 Oct 2013 14:13:46 GMT

I use this advanced aggregation

IF(AVG(AGGR(count(A)\count(B), D1,D2,D3)>100, 100, AVG(AGGR/count(A)\count(B), D1,D2,D3)))

but when the value of the currency if > 100 does not do it for single value but rather for the total for example

I have two values

90.3 and 136 the expression result is 113.15

I expect that the expression of the average

90.3 and 100 the result is 95.15