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

[jorgen_santberg]

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!

[GOKULAKANNAN]

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]

is that same rule for Product C and D?

[qv_testing]

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;

[jorgen_santberg]

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]

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.