Is it possible to use Aggr() inside nested set analysis?

[LDR]

Hello everybody,

I was struggling with the formula below during several hours (reading & testing, ...) with no luck:

SUM({<[Delivery Month]={"$(=Aggr(Max({<[Quantity] ={[>0]} >}[Delivery Month]),[Part]))"}>}[Quantity])

What I want to obtain is: 

• Max({<[Quantity] ={[>0]} >}[Delivery Month]) --> The higher [Delivery Month] with [Quantity] higher than 0.
• Aggr(...,[Part]) --> I want to group by [Part]. If I don't group Qlik Sense uses the higher Month of the table, not by row.
• SUM([Quantity]) --> I want to obtain the quantity for the higher [Delivery Month]

So, the question is: Can I use Aggr() in nested set analysis? or maybe the approach must be different.

PS: I'm using the formula before for filling a column in a Table chart.

Thanks in advance

[hic]

Yes, it is possible to use Aggr() nested. You may get a performance hit, but that's it.

However, there are some things to be aware of:

• Set expressions and dollar expansions are calculated globally, i.e. NOT row per row. So they cannot get different values in different rows.
• Aggr() returns an array of values, that in turn needs to be aggregated. In your expression you have a naked Aggr() which will return one value per [Part]. This needs to be aggregated.

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

[LDR]

Hi Henric,

Thanks for your reply. Reading your post and thanks to a colleague suggestion I found that the best approach for my requirement was to use FirstSortedValue() function. It's the first time I use it 😉

So, the formula below permits me to obtain what I want:

FirstSortedValue({<[Quantity] ={">0"}>}[Quantity],-[Delivery Month])

To be honest I don't know if I would be able to obtain the same result with my first approach but I will try it.

Have a good one

Regards