Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

katsafone
New Contributor III

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:

DivisionAreaMember
Region EastGraftonGeorge
Region EastNewcastleJack
Region EastByron BayJoe
Region WestBullamankaKatrina
Region WestBroken HillJoe
Region WestParkesBluey
Region WestAlburyBeachball
Region WestWentworthCostello
Region WestWilcanniaAbbott
Region WestDubboJack
Region WestGoulburnAmos
Region WestCobarJoe

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.

1 Solution

Accepted Solutions
MVP
MVP

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)

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)

14 Replies
bunchong_t
New Contributor II

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

This is what you looking for ?

firefox_2017-01-17_11-33-17.png

MVP
MVP

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)

katsafone
New Contributor III

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

MVP
MVP

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

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

katsafone
New Contributor III

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.

rahulpawarb
Valued Contributor II

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

katsafone
New Contributor III

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

MVP
MVP

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

Did you try in a text box?

Capture.PNG

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)

as expression could be helpful.

- Marcus

Community Browser