Count distinct product

[Anonymous]

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?

Thanks in advance.

[Gysbert_Wassenaar]

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.

[Anonymous]

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)

[Anonymous]

Hi Gysbert

Figure it out, instead of

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:

[Anonymous]

anyone got idea how to solve this problem?

[Gysbert_Wassenaar · Accepted solution]

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)

[Anonymous]

Thanks Gysbert

Thank you so much

[Anonymous]

1