topic Re: Nested aggregation not allowed in QlikView

Nested aggregation not allowed

brunolelli87 — Sat, 16 Nov 2024 18:33:49 GMT

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

Re: Nested aggregation not allowed

fosuzuki — Mon, 01 Jun 2020 15:03:58 GMT

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)

Re: Nested aggregation not allowed

brunolelli87 — Mon, 01 Jun 2020 15:18:55 GMT

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?

Re: Nested aggregation not allowed

Brett_Bleess — Thu, 25 Jun 2020 18:21:19 GMT

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