Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Gerhard
Contributor II
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.

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

Labels (1)
1 Solution

Accepted Solutions
Gerhard
Contributor II
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:
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

Gerhard_0-1694182719613.png

 

 

View solution in original post

2 Replies
agigliotti
Partner - Champion
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

Gerhard
Contributor II
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:
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

Gerhard_0-1694182719613.png