topic Counting customers with certain variables in orders in QlikView

Counting customers with certain variables in orders

Fri, 15 Jan 2016 13:31:30 GMT

Hi All,

I'm running into a problem. I want to count the number of customers who have ordered a certain set of clothes in their orders.

I at first made the following expression:

count({$<%ARTICLE_VARIANT = {'A','B*','C','D'}, CUSTOMER =

p({$<%ARTICLE_VARIANT = {'A'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'B*'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'C'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'D'}>} CUSTOMER)

>} DISTINCT CUSTOMER)

This gives me the number of customers who ordered all these articles. Now I have another set of different articles I would like to calculate as well. IE:

count({$<%ARTICLE_VARIANT = {'D','E*','F','G'}, CUSTOMER =

p({$<%ARTICLE_VARIANT = {'D'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'E*'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'F'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'G'}>} CUSTOMER)

>} DISTINCT CUSTOMER)

Now if I just sum these calculations I run into a problem. The result of the first can be 4 customers and of the second can be 4 customers, so the result would be 8. But what if a single customer ordered both sets. The result should be 7 instead of 8. How can I do this?

Re: Counting customers with certain variables in orders

Anonymous — Fri, 15 Jan 2016 17:12:14 GMT

Check out this description:

Combining logical operators in set analysis

Re: Counting customers with certain variables in orders

swuehl — Fri, 15 Jan 2016 17:17:52 GMT

Maybe try to create a combined set the contains both customer sub sets, then do the DISTINCT count:

count(

{$<%ARTICLE_VARIANT = {'A','B*','C','D'}, CUSTOMER =

p({$<%ARTICLE_VARIANT = {'A'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'B*'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'C'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'D'}>} CUSTOMER)

<%ARTICLE_VARIANT = {'D','E*','F','G'}, CUSTOMER =

p({$<%ARTICLE_VARIANT = {'D'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'E*'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'F'}>} CUSTOMER)*

p({$<%ARTICLE_VARIANT = {'G'}>} CUSTOMER)

DISTINCT CUSTOMER)

Re: Counting customers with certain variables in orders

Mon, 18 Jan 2016 11:46:37 GMT

This worked! Thanks!