Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Counting customers with certain variables in orders

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

3 Replies
jcooney1
Contributor III
Contributor III

Check out this description:

Combining logical operators in set analysis

swuehl
MVP
MVP

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)

View solution in original post

Not applicable
Author

This worked! Thanks!