topic Re: set analysis in QlikView

set analysis

mambi — Thu, 08 May 2014 10:34:31 GMT

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 ?

Re: set analysis

jagan — Thu, 08 May 2014 10:48:10 GMT

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.

Re: set analysis

tresB — Thu, 08 May 2014 10:53:50 GMT

For Difference(XOR), you may try :

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

Re: set analysis

mambi — Thu, 08 May 2014 10:58:26 GMT

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

Re: set analysis

jagan — Fri, 09 May 2014 01:46:54 GMT

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)

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

Hope this helps you.

Regards,

Jagan.

Re: set analysis

anbu1984 — Fri, 09 May 2014 13:48:04 GMT

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)

Re: set analysis

anbu1984 — Mon, 12 May 2014 07:33:20 GMT

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)