Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
McLaughlinMatt
Contributor II
Contributor II

Max count across a single dimension

This is driving me crazy. I have a straight table with  Orgs as the dimension and Count(Distinct MemberID) as the first expression:

OrgMemberCount
A15
B35
C24

 

What I want is another column for the Max MemberCount across the whole table like this:

OrgMemberCountMaxMemberCount
A1535
B3535
C2435

 

This seems like it should be incredibly simple but it's defeated me for hours.

Labels (4)
1 Solution

Accepted Solutions
Kushal_Chawda

If Member count is direct field you can use below

Max(total MemberCount)

If Member count is your expression then use below

Max(total aggr(Count(distinct MembeID), Org)) ----> Assuming  Count(distinct MembeID)  is MemberCount expression

 

View solution in original post

7 Replies
Kushal_Chawda

If Member count is direct field you can use below

Max(total MemberCount)

If Member count is your expression then use below

Max(total aggr(Count(distinct MembeID), Org)) ----> Assuming  Count(distinct MembeID)  is MemberCount expression

 

McLaughlinMatt
Contributor II
Contributor II
Author

I've tried that and it doesn't work.

Max(total aggr(Count(distinct MemberID), Org)) gives me:

OrgMemberCountMaxMemberCount
A1574
B3574
C2474

 

The TOTAL seems to be totaling the aggregation before I take the Max...

McLaughlinMatt
Contributor II
Contributor II
Author

OK. I figured out what's happening. There are memberIDs with NULL in the Org field due to a wonky join. So I wasn't seeing them in the dimension. Need to fix the join and then it will work as promised.

Kushal_Chawda

What is the membercount expression? I have assumed the expression. You can use your actual expression It will work

McLaughlinMatt
Contributor II
Contributor II
Author

This is what worked because it handled the null case correctly. NOTE the minus equals

Max(total aggr(Count({$<Org-={'*'}>}distinct MemberID), Org))

Kushal_Chawda

You don't need that. You can use suppress null value option if Org is null for Member IDs.

Kushal_Chawda

If you have NULL values then you need to use which you are using or blow

max(total aggr(Count({1-$<Org={'*'}>}ID),Org))