Nested aggregation not allowed with aggr

[alferrar]

Hi, probably this isn't the smoothest way to achieve it but...

I'm trying to limit the calculation of the delta between 2020 and 2019 only to the available 2020 weeks, but I'm receiving the nested aggregation error, and I can't see why

I know that the max(aggr(...) returns the correct value, and I also tried to set up a variable with the same error

any help wpuld be really appreciated, thanks

sum ( if (
max(aggr(if( sum( if(anno=2020, Pratiche ))>0, week), week)) <= week and anno=2020,
Pratiche ) )

/

sum ( if (
max(aggr(if( sum( if(anno=2020, Pratiche ))>0, week), week)) <= week and anno = 2019,
Pratiche ) )

-1

[Vegar]

You are getting nested aggregation errors because of the sum(max()). Both sum and max are aggregation functions.

In short you are doing this:

SUM(IF(MAX(AGGR(IF(SUM()))))

You can probably handle this issue by adding another aggr inside you outer SUM. It would look something like this:

SUM(AGGR(IF(MAX(AGGR(IF(SUM())))))

[alferrar · Accepted solution]

it worked this way:

sum (if( week<= max( total aggr(if(year=2020,week),week,year)) and year=2020 , Pratiche))

the trick was "total". The "total" makes it useless the aggr() so the above become the same of:

sum (if( week<= max( total if(year=2020,week)) and year=2020 , Pratiche))

which is a wiser method to obtain what I needed 

I don't really get it anyway, I mean, it wasn't a true nested aggregation from the beginning as it is working on the condition, and not the data and anyway I was using aggr().

Oh, well, anyway, solved. Thanks