Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Maximum count using AGGR function

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

CNCLNPOSLOS
1234512345121M
1234512345211N
1234512345311N
1234512345411N
2345623456114P
2345623456214P
2345623456319B
2345623456419B
2345623456519B
3456734567111N
3456734567211N
3456734567311N
4567845678122C
4567845678222C
4567845678325D
4567845678425D

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

So output should be

    

CNCount of POSPOSLOS
12345311N
23456319B
34567311N
45678222,25C,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

5 Replies
mayankraoka
Specialist
Specialist

Hi Srikanth,

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

Regards,

Mayank

tresesco
MVP
MVP

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), ',')

Capture.JPG

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Contributor
Contributor

Thank you, Very cool!