7 Replies Latest reply: Nov 19, 2013 3:38 AM by Tan Soo Chin

# Count distinct product

Hi

I want to count distinct product for Region A and Region B but only if the product exists in all the stores in the region.

Count for Region A= 2(C&E)

Count for Region B= 3(B&C&E)

Any idea how to achieve this?

• ###### Re: Count distinct product

sum(if(aggr(count(distinct total <Region> Store),Region,Product)=aggr(count(distinct Store),Region,Product),1,0))

see attached example.

edit: I don't know what you're looking at, but it can't be the example I posted. That shows 2 for Region A and 3 for Region B.

• ###### Re: Count distinct product

Hi Gysbert

Thanks for reply but count for Region A =2 not 11 and Region B=3 not 12 because i want to count only common product which exists in all the 5 stores in Region A and 3 Stores in Region B.

Count for Region A= 2 (all 5 stores in region A have Product C & Product E )

Count for Region B= 3 (all 3 stores in region B have Product B & Product C & Product E)

• ###### Re: Count distinct product

Hi Gysbert

sum(if(aggr(count(distinct total <Region> Store),Region,Product)=aggr(count(distinct Store),Region,Product),1,0))

i should put

sum(if(aggr(NODISTINCT count(distinct total <Region> Store),Region,Product)=aggr(count(distinct Store),Region,Product),1,0))

Thanks for solved my problem to get the regional count.

Now I can't get my global count correctly, can you help me on this as well?

All the 8 stores have common product C and E, then Global Count=2 (C&E)

Since product C & E are count in global level, so

Product Count for region A=2-2=0

Product Count for region B=3-2=1 (B)

I wish to get the result as below:

• ###### Re: Count distinct product

anyone got idea how to solve this problem?

• ###### Re: Count distinct product

Try:

sum(if(aggr(nodistinct count(distinct total <Region> Store),Region,Product)=aggr(count(distinct Store),Region,Product),1,0))-=[Regional Count]- count({<Product={'=count(distinct Store)=count(total distinct Store)'}>}distinct Product)

• ###### Re: Count distinct product

1