Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How would one go about displaying in a chart entries whose aggregate is similar to a selection?
I have a table with 2 fields: Organization and MemberID.
Organization | MemberID |
Org A | 1236793 |
Org A | 1652543 |
Org A | 1769724 |
Org B | 182349 |
Org B | 192436 |
... | ... |
IF you do Organization as the dimension and Count(MemberID) as the expression you get a chart with the number of members per organization.
Organization | Member Count |
Org A | 150 |
Org B | 224 |
Org C | 175 |
... | ... |
What I'm trying to do is have an expression that only shows organizations that are within 50 members of the selected organization for a "Similar sized organizations" chart. So if Org A has 150 members, I want to include any other organizations with between 100 and 200 members:
Similar Sized Organizations to Org A (150 Members)
Organization | Member Count |
Org C | 175 Members |
Org E | 125 Members |
Any help would be appreciated.
try below
create variable vOrg with below expression on front end
=GetFieldSelections(Org) // Org is your Organization field Name
then use below expression . ID is your Member ID field name
= if(getselectedcount(Org)>0,
count({<Org={"=count({<Org>-<Org={'$(vOrg)'}>}ID)>=count(total{<Org={'$(vOrg)'}>}ID)-1 and count({<Org>-<Org={'$(vOrg)'}>}ID)<=count(total{<Org={'$(vOrg)'}>}ID)+1"}>}ID),
Count(ID))
Updated formula
Figured it out with:
IF(Count({$<Org=> MemberID)<= Count(TOTAL {$<Org={"GetFieldSelections(Org)"}>} MeberID)*1.25 AND
Count({$<Org=> MemberID)>= Count(TOTAL {$<Org={"GetFieldSelections(Org)"}>} MeberID)*.75,
Count({$<Org=> MemberID))
try below
create variable vOrg with below expression on front end
=GetFieldSelections(Org) // Org is your Organization field Name
then use below expression . ID is your Member ID field name
= if(getselectedcount(Org)>0,
count({<Org={"=count({<Org>-<Org={'$(vOrg)'}>}ID)>=count(total{<Org={'$(vOrg)'}>}ID)-1 and count({<Org>-<Org={'$(vOrg)'}>}ID)<=count(total{<Org={'$(vOrg)'}>}ID)+1"}>}ID),
Count(ID))
Updated formula
Figured it out with:
IF(Count({$<Org=> MemberID)<= Count(TOTAL {$<Org={"GetFieldSelections(Org)"}>} MeberID)*1.25 AND
Count({$<Org=> MemberID)>= Count(TOTAL {$<Org={"GetFieldSelections(Org)"}>} MeberID)*.75,
Count({$<Org=> MemberID))