Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
McLaughlinMatt
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
Kush
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

McLaughlinMatt
Contributor II
Contributor II
Author

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

McLaughlinMatt
Contributor II
Contributor II
Author

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