14 Replies Latest reply: Jan 18, 2017 9:25 PM by Ozzie Boeuf

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

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.

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

This is what you looking for ?

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

Try below expression:

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

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

Hi Tresesco,

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

ozzie

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

Did you try in a text box?

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

Hi Tresesco,

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

i'll give it another try.

thanks again,

OZzie

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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

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

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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.

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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)

- Marcus

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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

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

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

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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.

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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

• ###### Re: What is a suitable Set Analysis expression for distinct counts based on condition/groupings

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