Problem Avg and AGGR

[Anonymous]

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]

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]

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]

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