Set Analysis Distinct Count in Qlik Sense

[njimack]

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!

[edwin · Accepted solution]

Try the exclude function:

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

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

[sunny_talwar · Accepted solution]

May be try something like this

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

[njimack]

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?

[edwin]

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

[njimack]

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

[edwin]

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.

[njimack]

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.

[njimack]

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

[njimack]

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