topic Expression for similar SUMs in QlikView

Expression for similar SUMs

McLaughlinMatt — Fri, 17 Jul 2020 20:07:49 GMT

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.

Re: Expression for similar SUMs

Kushal_Chawda — Fri, 17 Jul 2020 21:38:36 GMT

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

Re: Expression for similar SUMs

McLaughlinMatt — Thu, 23 Jul 2020 18:33:26 GMT

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