Nested aggregation not allowed issue

[googel84]

Hi community, I am facing a quite strange issue, at least to my eyes.

I am setting the following formula while in a script variable, as I did many other times.

SET vFatturatoYTD_AnnoPiuRecente =

     "sum({$< DATA_MOV_MAG.ANNO = {'=$(vAnnoPiuRecente)'} >}

          if( inyeartodate(

                    DATA_MOV_MAG,

                    makedate($(vAnnoPiuRecente),num(month('$(vDataBase)')),num(day('$(vDataBase)'))),

                    0),

              VALORE_EURO,

               0

          )

     )";

where

vAnnoPiuRecente = "max(DATA_MOV_MAG.ANNO)"

and vDataBase is calculated as the date of the main QVD last reload minus 1.

This time, though, when it comes to use the variable in question in a chart (or even in a text object), I get the following error:

Nested aggregation not allowed

For this reason, I was forced to define multiple expressions very similar to the formula cited above, one for each chart I would have wanted to use the variable. Here it is the expression:

sum({$< DATA_MOV_MAG.ANNO = {$(=$(vAnnoPiuRecente))} >}

     if( inyeartodate(

            DATA_MOV_MAG,

            makedate($(=$(vAnnoPiuRecente)),num(month('$(vDataBase)')),num(day('$(vDataBase)'))),

            0),

       VALORE_EURO,

       0

    )

)

See the attached sample application for further details.

Thanks in advance.

[Gysbert_Wassenaar · Accepted solution]

Try defining vAnnoPiuRecente like this:

SET vAnnoPiuRecente =  "=max(DATA_MOV_MAG.ANNO)";

The extra = sign in front of the max will cause the variable to be calculated outside the context of the expression it's used in.

If you do need it to be calculated in context of the chart then you need to use the aggr function. Basically sum(max(Value)) cannot be calculated without additional information. So you need something like sum(aggr(max(Value),Dim1, Dim2, ..., DimX)). That way you get a list of max values per Dim1,Dim2,...,DimX combination. That is a list of values that can be summed.