Return count of records in two categories by another dimension

[Gerhard]

Hi,

I have this data. using set analysis I want to count how many customers purchased product A in Order_Number 1 AND 2.

load * Inline [
Customer,Order_Number,Product
101,1,A
101,1,B
102,1,B
103,1,B
101,2,A
102,2,A
104,1,A];

I then want to display it in a table by product dimension.

Product|Count of customers

A              | 1

B              |0

I tried using intersection for the AND piece, but the problem this returns how many customers ordered in Order_Number 1 and 2. Irrelevant of the product.

Count(distinct{<Product={"=Count(distinct{<Order_Number={'1'}>}Order_Number)"}>*<Product={"=Count(distinct{<Order_Number={'2'}>} Order_Number)"}>} Customer)

Any help would be appreciated.

Thanks

Gerhard

[agigliotti]

Hi @Gerhard ,

Maybe this:

=Count( {< Customer = {"=count( {< Order_Number = {'1'} >} distinct Order_Number)>0"} * {"=count( {< Order_Number = {'2'} >} distinct Order_Number)>0"} >} distinct Customer )

I hope it can helps.

Best Regards

[Gerhard · Accepted solution]

Thanks for the reply, unfortunately the suggestion does not make a difference.

We do now have a solution as below, not set analysis but rather in the script.

Table1:
load * Inline [
Customer,Order_Number,Product
101,1,A
101,1,B
102,1,B
103,1,B
103,2,B
101,2,A
102,2,A
104,1,A
104,2,A]
;

Customers:
LOAD Count(Customer&Product) As CustomerCount,
Product,
Customer
Resident Table1
Group By Product,Customer;
Drop Table Table1;
Exit Script