0

# Compare distinct count of Maximum Date to previous year in Crosstab

Jul 10, 2017 at 11:24 PM

61

I have a crosstab which shows a distinct count of "unique patients" for their "usual doctor".

Our database has no field for "usual doctor", so I created a formula that says the "usual doctor" is the "last doctor who saw the patient" (or maximum date of service).

This works great if including just the current year. However, my administrator wants me to compare last year to this year in one report (crosstab).

When I create the crosstab, the "current year" counts are correct. However, the "previous year" counts are too small, because if the "maximum date seen" happens to be in the current year, it will show up in the "current year" column only, since Crystal will only count this once.

Any way to "reset" the "maximum date seen" so each year is evaluated separately, and a patient can "be counted twice" if they were seen in both years?

Thanks for any help!

Please show your formula for usual doctor

Ian

The rows of the crosstab list each doctor's name. The columns divide appointments into "current year" or "previous year", based on this formula field called {@YearComparison]:

If {vwGenSvcInfo.Service_Date_From} < date(year(dateadd("m", -12, currentdate)), month(dateadd("m", -12, currentdate)

), 1) then "*Previous Year"

else if {vwGenSvcInfo.Service_Date_From} > date(year(dateadd("m", -24, currentdate)), month(dateadd("m", -12, currentdate)

), 1) then "Current Year"

The formula field being summarized in the crosstab (as a sum) is called {@UsualDoctor}

{@UsualDoctor} does a distinct count of the patient's visits, based on the most recent appointment they have had:

If {vwGenSvcInfo.Service_Date_From} = Maximum ({vwGenSvcInfo.Service_Date_From}, {vwGenSvcInfo.Patient_Number}) then 1

else 0

Cheryl Warner Jul 12, 2017 at 04:42 PM
0

Ian, thanks for your suggestions. You gave me some good ideas.

I actually was able to get this to work and summarize correctly in a Crosstab!!

Here is how I solved it. Hopefully, it will help others. (Sorry it is a little lengthy, but I tried to explain things well!)

Report Logic:

1. Formula Field:YearComparison

//Groups Patient Service Dates into a complete 12 month timeframe of the Current Year or Previous Year.

If {vwGenSvcInfo.Service_Date_From} < date(year(dateadd("m", -12, currentdate)), month(dateadd("m", -12, currentdate)

), 1) then "*Previous Year"

else if {vwGenSvcInfo.Service_Date_From} > date(year(dateadd("m", -24, currentdate)), month(dateadd("m", -12, currentdate)

), 1) then "Current Year"

2. Formula Field:CurrentYear

//Filter that includes Service Dates in the Last 12 Complete Months.If True, output is Date of Service.

If {vwGenSvcInfo.Service_Date_From} > date(year(dateadd("m", -24, currentdate)), month(dateadd("m", -12, currentdate)

), 1) then {vwGenSvcInfo.Service_Date_From}

3. Formula Field:PreviousYear

//Filter that includes Service Dates 2 years ago, but includes only dates in 12 Complete Months of that year.If True, output is Date of Service.

If {vwGenSvcInfo.Service_Date_From} < date(year(dateadd("m", -12, currentdate)), month(dateadd("m", -12, currentdate)

), 1) then {vwGenSvcInfo.Service_Date_From}

4. Formula Field:UsualDoctor

//If Service Date is in the Previous Year, Find the "Most Recent" Date of Service for that Patient.Output is Most Recent Date concatenated with Patient Number.

//If Service Date is in the Current Year, Find the "Most Recent" Date of Service for that Patient.Output is Most Recent Date concatenated with Patient Number.

//This ensures "2" maximum Service Dates (1 for previous year, 1 for current year).Otherwise, Current Year would be the only result, making Previous Year totals short and inaccurate.

If {vwGenSvcInfo.Service_Date_From} = {@PreviousYear} then Maximum ({@PreviousYear},{vwGenSvcInfo.Patient_Number}) & " " & {vwGenSvcInfo.Patient_Number}

else if {vwGenSvcInfo.Service_Date_From} = {@CurrentYear} then Maximum ({@CurrentYear},{vwGenSvcInfo.Patient_Number}) & " " & {vwGenSvcInfo.Patient_Number}

5. Formula Field:DOSPt#

//Combines Service Date and Patient Number as 1 Field.This will include all Service Dates for that year, not just the maximum Service Date.

//Will be used to compare to "Usual Doctor" formula field result.

{vwGenSvcInfo.Service_Date_From} & " " & {vwGenSvcInfo.Patient_Number}

6. Formula Field:DoctorUniquePt

//Compares "Usual Doctor" result with "DOSPt#" result.This ensures the "Correct" doctor is the "only" doctor assigned to this patient.

//If this formula is NOT used, Patients will be assigned to "more than 1 doctor", if they have seen multiple doctors.This would then create inaccurate total counts.

//A "bottom N" sort will be performed on the Crosstab to eliminate all fields with the "blank" result from this formula.

If {@UsualDoctor} = {@DOSPt#} then {vwGenSvcInfo.Actual_Dr_Last_Name}

else " "

g0vuy.png (10.6 kB)
x9hja.jpeg (18.7 kB)
Share
Ian Waterman Jul 11, 2017 at 03:52 PM
0

I think you will have to change report from using the Crystal standard Cross Tab to a manual cross tab

You will need to create two columns for current and prior year and then group data on Patient Grp1 and your Year comparison formula.Grp2. Suppress Header and footer for Grp2

Create a usual doctor formula based on Year comparison

If {vwGenSvcInfo.Service_Date_From} = Maximum ({vwGenSvcInfo.Service_Date_From}, {@YearComparison}) then 1

else 0

However, you will not be able to summarise this for a count by patient, instead you will need to sum using a variable.

Vars will need to be reset in patient group header evaluated in detail

@Eval

Whileprintingrecords;

Global numbervar Previous;

Global numbervar Current;

If YearComparison = Previous and {vwGenSvcInfo.Service_Date_From} = Maximum ({vwGenSvcInfo.Service_Date_From}, Yearcomparison) then Previous:= Previous+1;

Repeat logic for current year

You may need to play about with logic to get it to work as required

In Patient Group footer you will need two formula like this

@DisplayPrev

Whileprintingrecords;

Global numbervar Previous;

Good luck

Ian

Show 1 Share

Cheryl Warner Jul 11, 2017 at 06:24 PM
0

Thanks for the suggestions, but I couldn't get it to work.

Is there any other way besides a manual crosstab?

Share
Ian Waterman Jul 12, 2017 at 08:00 AM
0

You could try grouping on a datefield with section printed for each year and then suppress that group in crosstab

You will then need to change your usual dr formula to cater for the Year group. But I think Cross tab might object to using a summary in a summary.

Ian

Share