aggr & count for particular product and its measurement larger than 0

[coloful_architect]

trying to build a set expression to count  product A's customer count for each customer as long as its sales larger than 0

I have a table structure like this , so there are multiple products, and multiple customer as well as multiple month year

I just care product A

product	Customer Full Name	MonthYear	Unit Sales
A	400011 CHILDRENS HOSPITAL, SAN DIEGO, CA	Aug-2022	700

I currently have this here 

Sum(Aggr(Count({<[Product]={'A'}, [Customer Full Name] {"=Sum([Unit Sales]) >0"}  >}DISTINCT [Customer Full Name]), MonthYear))

but it says calculation time out...not sure if sth wrong with my formula or justmy data is too big to run

[rubenmarin]

Hi, I think it's a typo in the question but this needs an equal: [Customer Full Name]={"=Sum([Unit Sales]) >0"}

Also note that each condition is individual, so a customer with -50 sales in product A and 100 in product B will meet the condition. And set analysis is not calculated for each aggr iteration, it will be calculated only once and for all the MonthYear and Products in the current selections

To calculate it by month (the same customer can be counted more than once, one for each month it meets the requierement) it would be:

Sum({<[Product]={'A'}>} Aggr(Count(DISTINCT If(Sum({<[Product]={'A'}>} [Unit Sales])>0, [Customer Full Name]), MonthYear))

[coloful_architect]

Hi Ruben, with that if condition, the formula no longer shows the calculation  timed error out

but column for that count shows all 0. anything I miss? is that because I build this into a table? 

product	Customer Full Name	MonthYear	Unit Sales	Count
A	address a	Aug-22	10	0
A	address b	Sep-22	30	0
A	address c	Aug-22	40	0

[rubenmarin]

Customer also needs to be added to the aggr:

Sum({<[Product]={'A'}>} Aggr(Count(DISTINCT If(Sum({<[Product]={'A'}>} [Unit Sales])>0, [Customer Full Name]), MonthYear,[Customer Full Name]))

[coloful_architect]

Hi Ruben,

the count column at the table chart still does not seem right. But I think we are close to the final solution.

I attach the sample excel here ...the column E is exact what I want. in this case, excel formula is easy.

would you pls play round this sample to see why the set expression still does not work?

[rubenmarin]

Hi, for that data sample this expression might work:

Count(TOTAL <[Customer Full Name]> {<Product={'A'},[Units Sales]={">0"}>} [Customer Full Name])