Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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...
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.
What is the membercount expression? I have assumed the expression. You can use your actual expression It will work
This is what worked because it handled the null case correctly. NOTE the minus equals
Max(total aggr(Count({$<Org-={'*'}>}distinct MemberID), Org))
You don't need that. You can use suppress null value option if Org is null for Member IDs.
If you have NULL values then you need to use which you are using or blow
max(total aggr(Count({1-$<Org={'*'}>}ID),Org))