topic Count with Set analysis - AND combination for same dimension in QlikView

Count with Set analysis - AND combination for same dimension

EliGohar — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I'm trying to count unique faces (FaceID field) which appeared both in “Left wing” and “Exit 1” Sources, this is a sample data:

if I’m using this expression:

count(distinct {$<[Source]={'Left Wing','Exit 1'}>} FaceID)

I’ll get 2 (because it calculates faces which appeared in “Exit 1” or “Left Wing”)

I'm expecting to get 1 because only faceID 2190 appeared both in "Exit 1" and "Left Wing"

Thanks for the help,

Eli.

Re: Count with Set analysis - AND combination for same dimension

sunny_talwar — Tue, 11 Aug 2020 13:12:52 GMT

Try this

Count(DISTINCT {$<FaceID = P({<[Source] = {'Left Wing'}>}) + P({<[Source] = {'Exit 1'}>})>} FaceID)

Re: Count with Set analysis - AND combination for same dimension

EliGohar — Tue, 11 Aug 2020 14:37:34 GMT

@sunny_talwar Thanks but it still counts faces which appeared in “Exit 1” or “Left Wing”.

Re: Count with Set analysis - AND combination for same dimension

sunny_talwar — Tue, 11 Aug 2020 14:44:00 GMT

Sorry my bad, try this instead

Count(DISTINCT {$<FaceID = P({<[Source] = {'Left Wing'}>}) * P({<[Source] = {'Exit 1'}>})>} FaceID)

changed '+' (union) to '*' (intersection)

Re: Count with Set analysis - AND combination for same dimension

EliGohar — Wed, 12 Aug 2020 06:04:34 GMT

@sunny_talwar You Rock!

Re: Count with Set analysis - AND combination for same dimension

EliGohar — Wed, 26 Aug 2020 05:46:55 GMT

@sunny_talwar I have another question:

I want to add another condition:

Count unique faces (FaceID field) which appeared both in “Left wing” and “Exit 1” Sources and not appeared in "Entrance 2" source.

Tried the following expression:

Count(DISTINCT {$<FaceID = P({<[Source] = {'Left Wing'}>}) * P({<[Source] = {'Exit 1'}>}) * P({<[Source] -= {'Entrance 2'}>})>} FaceID)

But no luck.

Any idea?

Thanks.

Re: Count with Set analysis - AND combination for same dimension

lironbaram — Wed, 26 Aug 2020 06:25:48 GMT

hi
try this

Count(DISTINCT {$<FaceID = P({<[Source] = {'Left Wing'}>}) * P({<[Source] = {'Exit 1'}>}) - P({<[Source] = {'Entrance 2'}>})>} FaceID)

Re: Count with Set analysis - AND combination for same dimension

EliGohar — Wed, 26 Aug 2020 06:39:03 GMT

@lironbaram

Tried it, it keeps counting people who visited all 3 areas, for example:

Re: Count with Set analysis - AND combination for same dimension

lironbaram — Wed, 26 Aug 2020 09:06:47 GMT

hi @EliGohar
strange , have a look at the attach app ,
this expression work as expected

Re: Count with Set analysis - AND combination for same dimension

EliGohar — Wed, 26 Aug 2020 10:51:14 GMT

@lironbaram Thanks!