Distinct Count using 2 fields in Crystal 1

[buffdaddy619]

Hello. I was wondering if it would be possible to get a distinct count of ID when a person has 1 or more rows for the same DATE.

For example, Bobby has 2 DATES of 7/9/2012. I would like to show a 1 for the first row and a 0 for the second.

This data is all on the "detail" level on CR 2008.

Any help would be appreciated. Thank you.

ID NAME DATE CODE VISITS

Z117079 BOBBY 7/9/2012 99204 1

Z117079 BOBBY 7/9/2012 POC14 0

 

[pmax9999]

Hi buffdaddy619

Create a formula that concatenates the date (in text format) with the name as follows:

ToText({Table.Date}, 'yyyyMMdd') + {Table.Name}

where {Table.Date} represents the field that contains the date and {Table.Name} contains the names.

Then do a distict count on this formula.

Hope this helps

Cheers
Pete

 

[buffdaddy619]

pmax9999,

Thank you for the response. That solution will give me a 1 for all of my rows. So based on the data example below I would like a formula that will look at the line below and, if the patient_id and the service date are the same, will give me a zero instead of a one. If I do a distinct count by combining the patient_id + service date I will get a one for each row.

Is this possible?

PATIENT_ID PATIENT SERVICE_DATE DISTINCT VISIT
Z117079 BOBBY 7/9/2012 1
Z117079 BOBBY 7/9/2012 0
Z636805 TOMMY 6/26/2012 1
Z636805 TOMMY 6/26/2012 0
Z648268 STEVE 6/11/2012 1
Z648268 STEVE 6/11/2012 0
Z648268 STEVE 6/11/2012 0
Z648268 STEVE 6/11/2012 0
Z648268 STEVE 6/12/2012 1
Z648268 STEVE 6/19/2012 1
Z648268 STEVE 7/3/2012 1
Z648268 STEVE 7/10/2012 1
Z648268 STEVE 8/10/2012 1

 

[MCuthill]

buffdaddy,

Not that I have to understand "why" to answer your posting, but I am unsure what you will then do with these results? Unless exported to excel, what you have outlined would not be able to be Summarized to the bottom to get a total patients for a date or anything. Might I ask what the end intent of this "Distinct Count" is (though without a group, it can only be a distinct count on the report.

If all you are looking for is a 1 or 0 to "Flag" the first instance of a person on a given day (which is how I have understood your request to be), you should just have to expand on what pmax suggested and evaluate this (concatenated) formula. Unfortunately, I do not have Crystal Reports in front of me today and cannot confirm the exactly syntax for below, but this should get you headed in the right direction.

{@FirstVisitbyPatientThisDate}

IF IsNull(Previous({@PMax'sFormula})) OR Previous({@PMax's Formula}) <> {@PMax's Formula} THEN 1 else 0

The reason for the null check is on the first record, there is no Previous() record... if this Null check doesn't work, there may be something like "RecordNumber" or something that can be used for the first record in the report.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

i do not have crystal immediately in front of me but something like this maybe?

IF onfirstrecord=TRUE then 1 else IF ({Patient_ID} = previous(Patient_ID}) and ({Service_Date} = previous({Service_Date})) then 0 else 1

 

[buffdaddy619]

Mike,

Thank you for your response. The report was initially just a data dump that would allow managers to export the results into excel. The CR is run off of a SQL Script that then populates just the detail section of the report. This worked fine. However one of the managers wanted a discount count of visits added to the report, where if the patient had 2 or more service dates for the same day, to count that only as one.

The managers have a formula I provided them in excel but wanted to know if I could just code this into the report to save them a step.

I will look over your solution now. Thank you again.

 

[buffdaddy619]

I just wanted to thank you all. This formula did the trick:

IF onfirstrecord=TRUE then 1 else
IF ({Command.PATIENT_ID} = previous({Command.PATIENT_ID})
and
({Command.SERVICE_DATE_DT} = previous({Command.SERVICE_DATE_DT}))) then 0 else 1

Looks like i learned something new today. I tip my hat to you all. You all have just made my life a bit easier.

Thank you

 

[lbass]

I think you have overcomplicated this. Pmax9999 presented the simplest solution, although I would have used the ID field like this:

{Table.Date}&" "&{Table.ID}

If you insert a distinctcount on this formula at the report level or at a higher order group level, it would show the number of unique visit dates by person. I think you must have inserted it at the wrong level.

Note that if your field is really a datetime, you would need to do this:

date({Table.Datetime})&" "&{Table.ID}

-LB