Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:  Contributor III

## Set Analysis Distinct Count in Qlik Sense

Hi All,

I'm trying to produce a KPI to show how many clients we serve in Europe that we don't serve elsewhere.  I've already defined a Master Measure Region_Count as Count( distinct Region).

My expression for No. of clients is:

``=count( distinct {<Region_Count={1}, Region = {"HBEU"}>} Mastergroup)``

However, this is showing all European clients, including those we serve elsewhere.  So it appears to be ignoring the first condition in the set analysis.  Can anyone see my (probably obvious) mistake?

Thanks!

Labels (1)
• ### General Question

2 Solutions

Accepted Solutions  Master

Try the exclude function:

``````count(distinct {<Region={'HBEU'}, Mastergroup=e({<Region-={'HBEU'}>}Mastergroup)>}Mastergroup)

count where Region = HBEU and excludes Mastergroup where Region <> HBEU``````  MVP

May be try something like this

``Sum({<Region = {"HBEU"}>} Aggr(If(Count(DISTINCT Region) = 1, 1, 0), Mastergroup))``

9 Replies  Master

Try the exclude function:

``````count(distinct {<Region={'HBEU'}, Mastergroup=e({<Region-={'HBEU'}>}Mastergroup)>}Mastergroup)

count where Region = HBEU and excludes Mastergroup where Region <> HBEU``````  MVP

May be try something like this

``Sum({<Region = {"HBEU"}>} Aggr(If(Count(DISTINCT Region) = 1, 1, 0), Mastergroup))``  Contributor III
Author

Wow thanks - both solutions did exactly what I asked for (and they're both far too complex for me to have figured out myself!)

How would I build on this, for example to sum my PBT field for those clients in Europe but no other Region?  Master

the ask "sum my PBT field for those clients in Europe but no other Region" pretty much sounds like the same thing.  you just replace the field

Mastergroup

with whatever field you want to sum.  if both solution worked for you then you can do either for the new field  Contributor III
Author

Simply replacing that field just returns a count, rather than the Sum.  Master

you are saying when you use the expression sum({<...>}PBT), it returns a count and not a sum?  i dont think thats a Qlik issue but a data issue.  to troubleshoot it, i suggest you create 2 measures: one with an actual count and one with a sum.  if there is no difference, you have to do a deeper dive.  Contributor III
Author

Here are my two expressions, which I've adapted slightly from the solutions I was given:

Count of Mastergroup:

``=Sum({<Region = {"HBAP"}, Sector -={"Exit", "Other GB&M"}, Product -={"Other"}>} Aggr(If(Count(DISTINCT Region) = 1, 1, 0), Mastergroup))``

Sum of PBT:

``=Sum({<Region = {"HBAP"}, Sector -={"Exit", "Other GB&M"}, Product -={"Other"}>} Aggr(If(Count(DISTINCT Region) = 1, 1, 0), PBT))``

Here's how it appears in the KPI: But here's the raw data.  PBT for those 6 clients should sum to \$22.5: You can see that the expression is counting the number of rows that have a distinct PBT value.  Contributor III
Author

I figured it out eventually...

``=Sum({<Region = {"HBAP"}, Sector -={"Exit", "Other GB&M"}, Product -={"Other"}>} Aggr(If(Count(DISTINCT Region) = 1, SUM(PBT), 0), Mastergroup)) ``  Contributor III
Author

Still struggling to get my head round this.

I'm now trying similar analysis for clients in 2 specific regions (HBEU and HBAP) but no other regions.  Here's my best attempt so far, but it's not working.  It's including clients that are in HBEU + LATAM or HABP + MENA 🙄

``````=Sum({
<Region = {"HBEU"}, Region -={"HBUS","LATAM","MENA"},Sector -={"Exit", "Other GB&M"}, Product -={"Other"}>
+<Region = {"HBAP"}, Region -={"HBUS","LATAM","MENA"},Sector -={"Exit", "Other GB&M"}, Product -={"Other"}>
}
Aggr(If(Count(DISTINCT Region) = 2, Sum(PBT), 0), Mastergroup))`````` Tags
Community Browser