Distinct Count

[ErnstNoto]

Hi!

I am having trouble with getting a correct summary with distinct counting.

The issue revolves around counting how many times a social security number has been credit checked. The problem I am having is that if a socicial security number has been creditchecked 2 times in one day, this should count as one, in which I can use a distinct count. But if it has been credit checked 3 times yesterday and 1 time today, that should count as two. A distinct count on this field will give the value one when grouping on date, but on the next group which is per month, it also returns the value 1 and also the same per year. But I need to summarize all the distinct summaries pr day.

I am not a super user in Crystal Reports, but I have usually found a solution through reading. This problem however I cant find a solution to. I
Hope my question was understandable and that someone might be able to help.

 

[ErnstNoto]

Here's an example of what my report showes now, and the summary I want shown in parenthese:

Month Day Social Security
March 130309 556174-1234*
130309 556677-2345*
130309 556724-3456*
Sum Per Month 3

Month Day Social Security
April 140409 556222-4567*
140409 556222-4567
140409 556735-5678*
140409 556735-5678
Sum Per month 2

Month Day Social Security
030509 556222-4567
Sum per Day 1
040509 556222-4567
Sum per Day 1
050509 556222-4567
Sum per day 1
Sum Per month 1 (Should be 3)

Month Day Social Security
June 060609 450816-6789*
Sum per month 1

Month Day Social Security
Oct 061009 551115-7890*
Sum per month 1

Total per Year 7 (Should be 10)

 

[Madawc]

Use a formula field to combine the social security number and the data, converted to text. Do a distinct count on that.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Or create a formula like this:

{table.date} & {table.ssn}

Insert a distinctcount on this.

-LB

 

[ErnstNoto]

Aah!

My date field contains a detailed timestamp as well as the date, but I then made a formula

Left ({date},10)

This way I removed the timestamp and only had the date itself left.

Then I do as suggested, combine this new date field with the ssn field

Solved!

clever

Thank you so much!

Best Regards Ernst
Merry Christmas