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]

‌Hi Tresesco,

I'm using the expression editor in Qlik Sense desktop 3.1 SR3

i'll give it another try.

thanks again,

OZzie

[Anonymous]

Hi Marcus,

I agree with you.  The standard KPI extension only allows a measure...

What I've decided to do is trap those entries during the ETL process through a derived field and use the following expression to display the result.

Count(DISTINCT {$<Division= ,[Multi-Directorate Members]={"Yes"}>} [Member])

As you can see, the above is derived from suggestions from you and our colleagues responding to my question.

Thank you for your assistance here.

Ozzie

[jonathandienst · Accepted solution]

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

The expression should read:

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

(sum, not count - count will return everything, but sum should work)

[Anonymous]

Hi Jonathan,

A small change to your formula and it works!!

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

This removes the need to do any additional ETL scripting to tag the member records in both divisions.

Thanks to all who took time out to help me.

[Anonymous]

Hi Bunchong,

Thank you for your time to respond here.  The formula that works for me is in my response to Jonathan below.

Kind regards,

Ozzie