topic Re: Count(Distinct ....) in QlikView

Count(Distinct ....)

Anonymous — Wed, 20 May 2015 18:56:07 GMT

I am trying to count the distinct number of prescriptions in a data base under a field [Script Number] and then calculate the market share based on the number of prescriptions. My results are as follows:

The Market TRx (number of market prescriptions per area) column is based on the expression Count (Distinct [Script Number]). The percentage share is based on the same expression i.e. Count (Distinct [Script Number]) but on a relative basis. The results are obviously wrong as the total prescription count of 220,524 is exceeded by the sum count of the individual values per category as above.

What could be wrong?

Regards.

Chris

Re: Count(Distinct ....)

Anonymous — Wed, 20 May 2015 19:05:41 GMT

The result may be not wrong. It only tells that many Script Numbers are counted in more than one Group.

Re: Count(Distinct ....)

Anonymous — Wed, 20 May 2015 19:36:51 GMT

Thanks Michael for your input,

Re: Count(Distinct ....)

Anonymous — Wed, 20 May 2015 19:39:37 GMT

I used the following expression posted by one Clever Anjos, since deleted.

sum(

aggr(

Count (Distinct [Script Number]),

[Script Number]

)

The above worked for me.

Thanks Clever

Re: Count(Distinct ....)

Clever_Anjos — Wed, 20 May 2015 19:52:00 GMT

weird thing, when I saved the post, got an error message,

Good to know it worked

Re: Count(Distinct ....)

Anonymous — Wed, 20 May 2015 20:09:53 GMT

Whatever works for you

I assume you'll get the same result with the original expression simply by changing total mode from "expression total" to "sum of rows" (only if it is a straight table of course).

Re: Count(Distinct ....)

Anonymous — Thu, 21 May 2015 16:21:49 GMT

Thanks Michael

Yes it is a straight table. Changing the total mode to 'sum of rows' gives a different result.

Regards

Chris