topic Re: First sorted value with aggr in App Development

First sorted value with aggr

maxattar — Thu, 15 Sep 2022 15:19:52 GMT

Hi,

I am trying to get the value glf_cod corresponding to the highest count of lic_cod for each glf_cod.

I always get a null result when trying the following :

FirstSortedValue(DISTINCT glf_cod,-Aggr(count(lic_cod),glf_cod))

I have tried with and without Distinct and there is no tie for the first value.

Does anyone have an idea of what I seem to be doing wrong please ?

Re: First sorted value with aggr

BrunPierre — Thu, 15 Sep 2022 15:51:02 GMT

It may be this

=FirstSortedValue(DISTINCT glf_cod,-Aggr(Count(lic_cod ),glf_cod),1)

Re: First sorted value with aggr

maxattar — Thu, 15 Sep 2022 16:03:38 GMT

Hi Peter,

Thanks for the answer but it still does not work :

Re: First sorted value with aggr

BrunPierre — Thu, 15 Sep 2022 16:28:43 GMT

=Max(AGGR(Count(DISTINCT glf_cod ),lic_cod ))

Re: First sorted value with aggr

maxattar — Mon, 19 Sep 2022 08:33:57 GMT

Hi Pierre, thanks for your help but this returns the lic_cod with the most glf_cod associated with it.

I'm looking for the glf_cod where there are the most lic_cod (kind of the other way aroung from what you proposed).

Max(AGGR(Count(DISTINCT lic_cod ),glf_cod )) would give me the highest count of lic_cod for the right glf_cod but I'm looking for the glf_cod, NOT the count associated with. FirstSortedValue would solve my problem but it keeps returning Null for a reason I don't get.