topic Re: Set Analysis Distinct Count in App Development

Set Analysis Distinct Count in Qlik Sense

njimack — Wed, 02 Nov 2022 18:54:39 GMT

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!

Re: Set Analysis Distinct Count in Qlik Sense

edwin — Wed, 02 Nov 2022 18:55:17 GMT

Try the exclude function:

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

Re: Set Analysis Distinct Count

sunny_talwar — Wed, 25 May 2022 16:55:32 GMT

May be try something like this

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

Re: Set Analysis Distinct Count

njimack — Tue, 31 May 2022 13:27:47 GMT

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?

Re: Set Analysis Distinct Count

edwin — Wed, 01 Jun 2022 16:15:25 GMT

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

Re: Set Analysis Distinct Count

njimack — Thu, 02 Jun 2022 12:17:57 GMT

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

Re: Set Analysis Distinct Count

edwin — Thu, 02 Jun 2022 18:30:27 GMT

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.

Re: Set Analysis Distinct Count

njimack — Mon, 06 Jun 2022 07:34:10 GMT

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.

Re: Set Analysis Distinct Count

njimack — Tue, 07 Jun 2022 07:57:16 GMT

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

Re: Set Analysis Distinct Count

njimack — Tue, 07 Jun 2022 14:02:32 GMT

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