Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
njimack
Contributor III
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)
2 Solutions

Accepted Solutions
edwin
Master II
Master II

Try the exclude function:

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

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

 

View solution in original post

sunny_talwar

May be try something like this

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

 

View solution in original post

9 Replies
edwin
Master II
Master II

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

May be try something like this

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

 

njimack
Contributor III
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?

edwin
Master II
Master II

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
Contributor III
Contributor III
Author

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

edwin
Master II
Master II

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
Contributor III
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:

njimack_1-1654500489346.png

But here's the raw data.  PBT for those 6 clients should sum to $22.5:

njimack_2-1654500772772.png

You can see that the expression is counting the number of rows that have a distinct PBT value.

njimack
Contributor III
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)) 
njimack
Contributor III
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))