What is a suitable Set Analysis expression for distinct counts based on condition/groupings

[Anonymous]

Hi,

I'm trying to create a suitable expression using the Qlik Sense KPI extension that returns a distinct count of  Members who appear in both Divisions (there are currently only 2).

An extract of my 5,000 row dataset looks like this:

Division	Area	Member
Region East	Grafton	George
Region East	Newcastle	Jack
Region East	Byron Bay	Joe
Region West	Bullamanka	Katrina
Region West	Broken Hill	Joe
Region West	Parkes	Bluey
Region West	Albury	Beachball
Region West	Wentworth	Costello
Region West	Wilcannia	Abbott
Region West	Dubbo	Jack
Region West	Goulburn	Amos
Region West	Cobar	Joe

Looking at this dataset, based on my requirements, the expression should return the correct answer of 2 (Jack and Joe - as they at least appear once in both Region East and Region West)

I'm stuck at the moment and started with this expression when maybe I should use set analysis:

aggr (count(Member), Division, Member ) )

Any help will be greatly appreciated.

Thanks.

[Anonymous]

This is what you looking for ?

[tresesco]

Try below expression:

Count( DISTINCT {<Member={"=Count(Distinct Division)=$(=count(Distinct Division))"}>} Member)

[Anonymous]

‌Hi Tresesco,

thank you for responding.  Unfortunately it doesn't return a value.

ozzie

[jonathandienst]

How about this?

Count(Aggr(If(Count(DISTINCT Division & Region) >= 2, 1, 0), Member))

Sum(Aggr(If(Count(DISTINCT Division & Region) >= 2, 1, 0), Member))

[Anonymous]

‌hi Jonathan,

thank you for your reply.  Unfortunately it returns the total number of unique members across the whole dataset (623)

I will say this, both of you guys have given me some ideas to look at.

thanks.

[rahulpawarb]

Hello Ozzie,

To get the member count who are present two times define below expression for KPI:

Sum(aggr(if(COUNT(Member)>1,1,0),Member))

Hope this will solve your problem.

Thank you!

Rahul

[Anonymous]

Hi Rahul,

Yes, I agree, but I have many multiples of members across the 5,000 entries.  Precisely, there are 623 unique members across the 5,000 records.  Nearly all are in one or the other Division (Region East or Region West).

I know there are a few members that belong to multiple areas in both Divisions - these are the ones I'm trying to trap and produce a distinct count of those members so that we don't count them more than once.

Thanks for your help here 🙂

Ozzie

[tresesco]

Did you try in a text box?

[marcus_sommer]

Based on the suggestion from Jonathan I could imagine that using a table-chart with:

Aggr(Count(DISTINCT Division & Member), Member)

as calculated dimension and:

count(Member)

as expression could be helpful.

- Marcus