Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count total distinct problem

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

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

14 Replies
MK_QSL
MVP
MVP

time is your cyclic group?

if yes, use like below...

count(distinct(StoreID))
/
count( total distinct (<$(=GetCurrentField(TIME)> StoreID
))

chiru_thota
Specialist
Specialist

count(distinct(StoreID))
/

count(distinct total<TIME> StoreID)

Not applicable
Author

This one gives always 100% result

Not applicable
Author

Yes its a cyclic group but doesn't seem to work..

MK_QSL
MVP
MVP

Have you tried below?

count(distinct(StoreID))
/
count(Distinct Total  (<$(=GetCurrentField(TIME)> StoreID
))

Not applicable
Author

yes, also no result it just doesn't show anything. Also fields after $ sign are grey. Should they?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

The correct syntax is:

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


In order to troubleshoot your expression, turn your chart into a Straight Table (or a Pivot Table) and leave the expression Label empty. You will be able to see exactly how QlikView renders your expression formula.


Oleg Troyansky


Come and learn advanced QlikView techniques from the best:

www.masterssummit.com

Not applicable
Author

Oleg,

the formula you provided indeed has no errors. However - still no results - no data is being displayed. Perhaps there is a different formula to get the same result?

thanks

Regards

CaptainP

Not applicable
Author

Oleg,

I have another strange problem - perhaps its a bug that results in not showing the formula you provided.

On my bar chart I have a formula count(distinct(StoreID))

Surpirisingly each product that i click on has different bar colours - this didn't happen before in case of other charts with similar structure

Any ideas on that?

Regards

CaptainP