Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mambi
Contributor III

set analysis

Hi everyone,

in the attached file i have 2 teams (EQUIPE) and a list of clients , so what i want to do is : using set analysis

in the first step to identify :

- the number of all distinct clients ( should be equal to 81) --> Ok in attached file

- the number of clients who belongs only to the team 'C' (should be equal to 3)  --> Ok in attached file

- the number of clients who belongs only to the team 'H'  (should be equal to 3) --> Ok in attached file

- the difference (XOR) of clients  (should be equal to 6) --> don't work

- the common clients between C and H  (should be equal to 75) -->  don't work

in the second step i want to get the list of clients for each number ( 81,3...)

any idea ?

Tags (2)
6 Replies
MVP
MVP

Re: set analysis

Hi,

Try below expressions

For XOR

=Count(DISTINCT CTID) - Count({<CTID = {'=Count(DISTINCT EQUIPE) > 1'}>} DISTINCT CTID)

For Common clients

=Count({<CTID = {'=Count(DISTINCT EQUIPE) > 1'}>} DISTINCT CTID)

Hope this helps you.

Regards,

Jagan.

MVP
MVP

Re: set analysis

For Difference(XOR), you may try :

=Count({<EQUIPE={'H'}, CTID=E({<EQUIPE={'C'}>} CTID)> /<EQUIPE={'C'}, CTID=E({<EQUIPE={'H'}>} CTID)>} DISTINCT CTID)

mambi
Contributor III

Re: set analysis

First step ok : it's works fine

but why just Count({<EQUIPE={'H'}> /<EQUIPE={'C'}>} DISTINCT CTID)  don't work for XOR ??

what about the second step please ? i want to get for each XOR, Common... the list of those clients

MVP
MVP

Re: set analysis

Hi,

For XOR you have try what Tresesco suggested or which I suggested. 

Count({<EQUIPE={'H'}> /<EQUIPE={'C'}>} DISTINCT CTID


The above expression won't give you the XOR.


Next if you wan to get the XOR, Common clients then try like this


Chart  1 (Straight Table): For common

Dimension : CTID

Expression


Chart 2 (Straight Table): For XOR

Count({<EQUIPE={'H'}, CTID=E({<EQUIPE={'C'}>} CTID)> /<EQUIPE={'C'}, CTID=E({<EQUIPE={'H'}>} CTID)>} DISTINCT CTID)


or


=Count(DISTINCT CTID) - Count({<CTID = {'=Count(DISTINCT EQUIPE) > 1'}>} DISTINCT CTID)


Hope this helps you.


Regards,

Jagan.

anbu1984
Honored Contributor III

Re: set analysis

Xor

=Count( {$<EQUIPE={'C'}, CTID=E({<EQUIPE={'H'}>} CTID)>} Distinct CTID) + Count( {$<EQUIPE={'H'}, CTID=E({<EQUIPE={'C'}>} CTID)>} Distinct CTID)

Common clients

=Count({<EQUIPE={'C','H'}>}Distinct CTID) - Count( {$<EQUIPE={'C'}, CTID=E({<EQUIPE={'H'}>} CTID)>} Distinct CTID) + Count( {$<EQUIPE={'H'}, CTID=E({<EQUIPE={'C'}>} CTID)>} Distinct CTID)

anbu1984
Honored Contributor III

Re: set analysis

Another solution

Xor

=Count({<CTID=P({<EQUIPE={'H'}>} CTID)> / <CTID=P({<EQUIPE={'C'}>} CTID)>} DISTINCT CTID)

Common clients

=Count({<CTID=P({<EQUIPE={'H'}>} CTID)> * <CTID=P({<EQUIPE={'C'}>} CTID)>} DISTINCT CTID)

Community Browser