Nested aggregation not allowed

[brunolelli87]

Hello guys,

I'm using the following expression on a table chart to collect the Growth Rate, and it's working perfectly:

=rate(3,0,-Avg({} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1),Avg({} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1))

My Table chart has the following dimension:

=if(Date(AsOfDVPG)>today(),'',Date(AsOfDVPG))

I'm trying to show the last available Growth Rate, when the user select a specific field. For that I'm trying to use the following expression:

= FirstSortedValue( rate(3,0,-Avg({} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1),Avg({} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1)) -DVPG, 1)

But I'm getting an error: "Error: Error in Expression: Nested aggregation not allowed" How can I do that?

How can I show the last available Growth Rate on a FieldBox?
In this example, I'm expecting to see 1.25%.

Thanks

[fosuzuki]

As you stated, it is not possible to directly nest two aggregation functions.

You have to use the AGGR() function "in between". A simpler example of how to use the AGGR() would be to get the sales amount for the top performing product: Max(AGGR(Sum(Sales), Product))

So your expression needs to be something like:

FirstSortedValue( AGGR( rate(3,0,-Avg({} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1),Avg({} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1)), [list of fields for the inner aggregation]),  -DVPG, 1)

[brunolelli87]

Hello,

Thanks for your attention and assistance.
I tried using the following expression, but it did not work. I just got a Dash as a result.

=FirstSortedValue( AGGR( rate(3,0,-Avg( LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1),Avg( LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1)), STCK, DVPG), -DVPG, 1)

 What's going on?

[Brett_Bleess]

Have a look at the following Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

Hopefully that may be of some further help.  It would most likely help if you attached a sample QVW file if you want further direct assistance, rather difficult to troubleshoot without that.  

Regards,
Brett