Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
jorgen_santberg
Contributor III
Contributor III

Count if sales product A larger than or sales product B larger than (measuring distribution)

Hi all,

 

I want to measure distribution of different brand. For example I have brand X with products A and B. If a customer buys more than 100 of product A or more than 20 of product B this should be counted as 1 (where sales of 50 and 60 of product A to customer C1 should be seen as 110). So in basic the folowing table with data:

Brand Product Sales Customer
X A 60 C1
X A 50 C1
X B 20 C1
X A 120 C2
X A 60 C3
X B 5 C3
X A 60 C4
X B 22 C4
X A 18 C5
Y C 120 C1
Y D 8 C2
Y D 15 C3
Y D 45 C3
Y D 25 C4
Y C 60 C4
Y C 110 C5
Y D 50 C6

 

The result should be:

Brand N
X 3
Y 5

 

 

Thanks in advance!

Labels (1)
5 Replies
GOKULAKANNAN
Creator II
Creator II

Hi @jorgen_santberg ,

We do have 4 products in the above table, if sum(sales) for A and C should be more than 100 then 1 or B And D count should be more than 20, either any one condition should satisfy, if 1st is satisfied then it should be counted in X and second should be counted in Y, is my understanding correct?

qv_testing
Specialist II
Specialist II

is that same rule for Product C and D?

qv_testing
Specialist II
Specialist II

As per my understanding,..

LOAD *, IF((Product='A' and Sales>100) or (Product='B' and Sales>20), 1,
IF((Product='C' and Sales>100) or (Product='D' and Sales>20), 1)) as Sales_Flag;
LOAD Brand,
           Product,
           SUM(Sales) as Sales,
           Customer
Resident Test
group by Brand, Product, Customer;

 

qv_testing_0-1706526118140.png

 

jorgen_santberg
Contributor III
Contributor III
Author

Yes, A and B are products for brand X and C and D are products for brand Y. If sum(Sales) for A for customer C1 is larger than 100 this counts as 1 for brand X. For brand Y and product C and D this is the same.

jorgen_santberg
Contributor III
Contributor III
Author

Thanks, but my data contains multiple saleslines on different dates and I want to see what my distribution was per brand in different years. The conditions of 100 and 20 are for the sum of the total year.