Announcements
cancel
Showing results for
Did you mean:
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

Labels (1)
• General Question

5 Replies
Creator II

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?

Specialist II

is that same rule for Product C and D?

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;
Product,
SUM(Sales) as Sales,
Customer
Resident Test
group by Brand, Product, Customer;

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.

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.