basic question#how to count distinct records in column

building reports crystal reports
1

example:
coulmn A: sample testing dates and there are some samples that were re-tested in later.

columnB: patientID
I would like to count unique test samples per month for the whole year :hb:

ColumnA ColumnB
2/6/2012 5771
2/10/2012 5761
2/13/2012 5781
3/6/2012 5799
4/9/2012 5771
4/10/2012 5800
4/11/2012 5761
5/6/2012 5771

champakdas (BOB member since 2012-06-19)

2

use:
=count([object];all) - for count with duplicates
= count([object];distinct) - for unique

pavan_cca :australia: (BOB member since 2009-04-06)

3

=Count([Number]) In ([Column B])

pavan_cca :australia: (BOB member since 2009-04-06)

4

Those responses work for WebI but not for Crystal…

Crystal includes DistinctCount as a summary type - just use that.

Is Column B the number of samples or the sample ID? If it’s the sample ID, you’ll need to group on the date field to get this to work. You can either set the group to show by the month instead of showing all of the dates and create a distinct count summary by using the summary button and setting the distinct count for the date group or you can create a formula that will look something like this:

DistinctCount({MyTable.ColumnB}, {MyTable.ColumnA}, “monthly”)

-Dell

hilfy :us: (BOB member since 2007-04-16)