topic Re: Max count across a single dimension in QlikView

Max count across a single dimension

McLaughlinMatt — Sat, 16 Nov 2024 18:19:20 GMT

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

Org	MemberCount
A	15
B	35
C	24

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

Org	MemberCount	MaxMemberCount
A	15	35
B	35	35
C	24	35

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

Re: Max count across a single dimension

Kushal_Chawda — Thu, 23 Jul 2020 20:21:58 GMT

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

Re: Max count across a single dimension

McLaughlinMatt — Thu, 23 Jul 2020 20:30:00 GMT

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

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

Org	MemberCount	MaxMemberCount
A	15	74
B	35	74
C	24	74

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

Re: Max count across a single dimension

McLaughlinMatt — Thu, 23 Jul 2020 20:33:46 GMT

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.

Re: Max count across a single dimension

Kushal_Chawda — Thu, 23 Jul 2020 20:37:55 GMT

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

Re: Max count across a single dimension

McLaughlinMatt — Thu, 23 Jul 2020 20:41:09 GMT

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

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

Re: Max count across a single dimension

Kushal_Chawda — Thu, 23 Jul 2020 20:46:53 GMT

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

Re: Max count across a single dimension

Kushal_Chawda — Thu, 23 Jul 2020 21:10:01 GMT

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

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