Count(Distinct ....)

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Thanks Michael for your input,

[Anonymous · Accepted solution]

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

[Clever_Anjos]

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

Good to know it worked

[Anonymous]

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).

[Anonymous]

Thanks Michael

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

Regards

Chris