topic Re: Count distinct product in QlikView

Count distinct product

Anonymous — Sun, 21 Jul 2013 06:25:05 GMT

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.

Re: Count distinct product

Gysbert_Wassenaar — Sun, 21 Jul 2013 09:29:15 GMT

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

Anonymous — Sun, 21 Jul 2013 10:27:38 GMT

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

Anonymous — Mon, 22 Jul 2013 01:43:58 GMT

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:

Re: Count distinct product

Anonymous — Tue, 23 Jul 2013 08:47:52 GMT

anyone got idea how to solve this problem?

Re: Count distinct product

Gysbert_Wassenaar — Tue, 23 Jul 2013 10:25:53 GMT

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

Anonymous — Tue, 23 Jul 2013 14:18:11 GMT

Thanks Gysbert

Thank you so much

Re: Count distinct product

Anonymous — Wed, 28 Aug 2013 13:54:55 GMT