topic Re: Qlikview set analysis in QlikView

Qlikview set analysis

Saranya1 — Fri, 15 Nov 2024 23:43:31 GMT

How we can count common records if we filter out two values in a field?

For e.g i am calculating count(<{category={'a'}>number)

count(<{category={'b'}>number)

now if I want to have the count of number that belongs to both a and b categories what will be the logic that involves some 'AND' condition or common count that belongs to both categories?

I Tried below

count(<{category={'a'},{'b'}>number) but it works like OR logic whereas I want only common records

Re: Qlikview set analysis

jwjackso — Wed, 28 Oct 2020 18:53:16 GMT

Using the data below:

Data:
Load * Inline [
dept,category,number
d1,a,100
d1,b,200
d2,b,200
d2,c,200
];

I added a third field, dept.

Try using the P() function

=sum({<dept=P({<category={'a'}>}dept)*P({<category={'b'}>}dept)>}number)

Re: Qlikview set analysis

Oleg_Troyansky — Wed, 28 Oct 2020 19:16:18 GMT

Hi,

You can use the function P(), as suggested before, or you can apply Boolean intersection to two Set Identifiers, each one with its own Set Modifier, like this:

count(<{category={'a'}> * <{category={'b'}> number)

Also, I'd like to invite you to my lecture on Set Analysis and AGGR, that I will be delivering on-line on November 18th. You will learn many valuable techniques that can be used for advanced analytics, using AGGR and Set Analysis. Check out the link to the Masters Summit for Qlik in my signature below.

Re: Qlikview set analysis

Saranya1 — Thu, 29 Oct 2020 14:36:13 GMT

Hey Thanks a lot 🙂 This actually worked and it fixed my issue 🙂

Re: Qlikview set analysis

Saranya1 — Thu, 29 Oct 2020 14:38:27 GMT

I tried the intersection logic but it did not work in my case since I have multiple filters I guess...

And yes function P() worked !