Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Return count of records in two categories by another dimension

Hi,

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

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

Labels (1)

1 Solution

Accepted Solutions
Contributor II
Author

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:
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:
Product,
Customer
Resident Table1
Group By Product,Customer;
Drop Table Table1;
Exit Script

2 Replies
Partner - Champion

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

Contributor II
Author

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:
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: