topic Maximum count using AGGR function in QlikView

Maximum count using AGGR function

Anonymous — Mon, 17 Sep 2018 05:23:54 GMT

Hi,

I have a requirement wherein I have to get the the maximum count in a chart and display related values for it.

I am able to get the Maximum count but not sure, how to get the related values.

Here is my sample requirement.

CN: Claim Number CLN: Claim Line Number POS: Place of Service LOS: Level of Service

I have to get POS that is most common in each Claim and get the POS and LOS.

So output should be

CN	Count of POS	POS	LOS
12345	3	11	N
23456	3	19	B
34567	3	11	N
45678	2	22,25	C,D

I am able to get the maximum Count of POS (2nd column) by using the following expression

MAX(AGGR(COUNT(POS),CN,POS)

But I am not able to get the related POS and LOS.

Is there anyway to get it from the chart itself instead of doing a COUNT and GROUP BY in the script.

Any help would be appreaciated.

Thanks,

Srikanth

mayankraoka — Mon, 17 Sep 2018 06:41:43 GMT

Hi Srikanth,

If possible please attach sample qvw so that we can help you quickly.And what number you are expecting here?

Regards,

Mayank

tresB — Mon, 17 Sep 2018 08:48:30 GMT

May be like this?

=Concat(DISTINCT Aggr(If(Count(POS) = Max(TOTAL <CN> Aggr(Count(POS), CN,POS)), POS), CN,POS), ',')

=Concat(DISTINCT Aggr(If(Count(POS) = Max(TOTAL <CN> Aggr(Count(POS), CN,POS)), LOS), CN,POS), ',')

Anonymous — Mon, 17 Sep 2018 09:51:29 GMT

Hi,

max(Aggr(Count(POS),POS,CN))

FirstSortedValue({<CLN={"=max(Aggr(Count(POS),POS,CN))"}>}POS,-CLN,1)

FirstSortedValue({<CLN={"=max(Aggr(Count(POS),POS,CN))"}>}LOS,-CLN,1)

Anonymous — Mon, 17 Sep 2018 15:57:04 GMT

Thank you Tresesco!!

It worked. I was just using the below function while I was trying without the TOTAL qualifier.

Max(Aggr(Count(POS), CN,POS))

I guess the TOTAL qualifier with TOTAL<CN> aggregates with just the CN field.

Thanks,

Srikanth

michaelrosen — Fri, 06 Mar 2020 20:29:31 GMT

Thank you, Very cool!