Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Expression for similar SUMs

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.

OrganizationMemberID
Org A1236793
Org A1652543
Org A1769724
Org B182349
Org B192436
......

 

IF you do Organization as the dimension and Count(MemberID) as the expression you get a chart with the number of members per organization.

OrganizationMember Count
Org A150
Org B224
Org C175
......

 

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)

OrganizationMember Count
Org C175 Members
Org E125 Members

 

Any help would be appreciated.

2 Solutions

Accepted Solutions
Highlighted
MVP
MVP

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 

 

 

 

View solution in original post

Highlighted
Contributor II
Contributor II

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

 

View solution in original post

2 Replies
Highlighted
MVP
MVP

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 

 

 

 

View solution in original post

Highlighted
Contributor II
Contributor II

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

 

View solution in original post