6 Replies Latest reply: Aug 6, 2015 9:57 AM by Srikanth P

# Exclude transactions with set analysis

Dear community,

I have a lot of transactions, in which a customer can buy products of the category A, category B, or offcourse both.

Now I am interested in how many customers buy ONLY A, ONLY B, and I would like to know how many customers buy BOTH A and B.

Although I assume its a very easy set analysis expression, I cant get it right.

COUNT(DISTINCT Transaction) would give me al transactions,

COUNT({<Category={"A"}>}DINSTINCT Transactions) would give me all transactions with customers buying product A or A and B combined.

But I want only those that buy A and NOT B. And those that combine A and B.

Any suggestions would be highly appreciated!

• ###### Re: Exclude transactions with set analysis

Try this may be:

For A and not B

=Count({<Category = {"=Count({<Category = {'A'}>}Transaction) = Count(Transaction))"}>} Transactions)

=Count({<Transaction = {"=Count({<Category = {'A'}>}Transaction) = Count(Transaction))"}>} Transactions)

For B and not A

=Count({<Transaction = {"=Count({<Category = {'B'}>}Transaction) = Count(Transaction))"}>} Transactions)

For Both A and B

=Count({<Transaction = {"=Count({<Category = {'A', 'B'}>}Transaction) = Count(Transaction))"}>} Transactions)

• ###### Re: Exclude transactions with set analysis

can you please post a sample application?

• ###### Re: Exclude transactions with set analysis

Hi,

When customers by A and B.

```Count({1<OrderID=P({1<Category={'A'}>}) * P({1<Category={'B'}>})>} OrderID)
```

Don't worry, be Qlik.

Tonial.

• ###### Re: Exclude transactions with set analysis

Hi,

one example app using the already proposed p() function:

```tabSales:
'Cust'&AutoNumber(Ceil(Rand()*30)) as Customer,
Chr(65+Rand()*2) as Category
AutoGenerate 60;
```

hope this helps

regards

Marco

• ###### Re: Exclude transactions with set analysis

how many customers buy category a and b?

=sum(if(aggr(concat({\$ <Category={A,B}>} DISTINCT Category, '', Category), Customer)='AB', 1))

a, not b?

=sum(if(aggr(concat({\$ <Category={A,B}>} DISTINCT Category, '', Category), Customer)='A', 1))

• ###### Re: Exclude transactions with set analysis

the other way generate flags in the script level so its easy on front end.