Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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?
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