Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

14 Replies
Anonymous
Not applicable
Author

‌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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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
Not applicable
Author

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