Count total distinct problem

[#1]

Hi

i'm trying to create a chart that shows to how many stores each product is sold.

X axis should show "Time" dimension (days / weeks / months) and Y axis should show two expressions:

- Total number of stores to which each product has been sold at least once in a period - for that I'm using function "count(distinct(StoreID))"  which works ok

- What % of the total store base that product was sold to. I was trying with the function below but it doesn't seem to work. Any suggestions?

count(distinct(StoreID))
/
count( total distinct (<Time> StoreID))

Many thanks

[MK_QSL]

Dear, I think the answer given by OLEG is OK but if it is not working, you need to upload you sample data or qvw file.

This is the best way to get answer in quick fashion.

[er_mohit]

Try this

count(distinct(StoreID))
/
count( total<Time> StoreID)

[#4]

this one always gets result 100%. So no matter what product I click on I always have 100% which is not really the case

[Oleg_Troyansky · Accepted solution]

Captain,

I think we've been trying to solve different issues... In our responses, we tried to show you how to calculate the share of the TOTAL, i.e. disregard the dimension value in the denominator.

What you are trying to do is to calculate the percentage of the Store count by product, compared to all the stores in general. So, in addition to disregarding the Dimension values, you want to disregard the selection of the Product. The corresponding formula would be:

count(distinct StoreID)
/
count({<Product = >}  Distinct Total  <[$(=GetCurrentField(TIME))]> StoreID)

This Set Analysis condition will cause the expression to disregard any selections made in the field Product. If you need to disregard any other selections, add them into the list of Modifiers, separated by coma,

Site comment to you and Manish:

DISTINCT is not a function, but rather a qualifier within an aggregation function. Therefore, it doesn't require a set of parenthesis after it. So, instead of :

count(distinct(StoreID))

it's enough to write:

count(distinct StoreID)

The extra set of parenthesis doesn't break the formula but makes it more cluttered and less readable.

cheers,

Oleg Troyansky

www.masterssummit.com

[#6]

Oleg,

thank you. Finally an answer that works

Best regards!