Skip to Content

How to do a conditional count in a crosstab

I have a crosstab that shows total number of patient visits for all doctors by doing a distinct count of patients (patient ID numbers).

I need to get a distinct count of patients seen by their "usual" doctor, and assign these counts to the correct doctor in the crosstab.

My criteria for "usual doctor" is counting how many appointments the patient has had per doctor, and whichever doctor has seen them the most times is considered "their usual doctor".

This is my formula:

Count ({vwApptDetail.Patient_Number}, {vwApptDetail.Resource_Abbr})

I am having trouble figuring out how to show the correct values in my crosstab, so a patient is "ONLY counted once" and "ONLY assigned to one doctor.

Any help is appreciated!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Oct 27, 2016 at 05:10 PM

    I found a workaround!

    1. I changed my criteria for finding the "usual doctor" to be "the last doctor who saw the patient". This was actually better criteria for our purposes.

    2. I grouped all appointments by "Patient Number" then sorted by appointment date.

    3. Then I created a formula field called "UsualDoctor":

    If ({vwGenSvcInfo.Patient_Number} = next ({vwGenSvcInfo.Patient_Number}))

    and ({vwGenSvcInfo.Service_Date_From} <= next({vwGenSvcInfo.Service_Date_From}))
    then "zzRepeats" else {vwGenSvcInfo.Actual_Dr_Last_Name}

    This formula field assigns a "Usual Doctor" to a patient based on which doctor saw them last. All other visits are assigned to "zzRepeats".

    4. I added the "UsualDoctor" formula field to the row of my crosstab and my summary cell is a distinct count of patient numbers.

    5. When doing this, the "zzRepeats" also shows up as a "doctor" and inflates the Grand Total of the crosstab. To fix this, I used the "Row Group Sort Expert" to sort by the "Bottom N" where N is 12. This shows all 12 doctors but discards the "zzRepeats" since that number is always going to be much larger than any of the individual doctor totals.

    Hope this may be helpful information to anyone else with a similar problem!

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 18, 2016 at 09:33 PM

    Hi Cheryl,

    This gets a bit complicated because you will need to determine the "usual doctor" before you can use the crosstab otherwise you will can have the same patient with multiple doctors.

    The question would be how to determine a patient's usual doctor. You're using a view right now which is good. I would recommend having your view do the determination instead of Crystal. Either that or have a new view that lists the patients and their usual doctor and linking them in Crystal.

    What you want does require some processing of data before Crystal can create the crosstab for you.

    Good luck,


    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2016 at 02:12 PM

    Thanks, Brian.

    I was hoping to use my formula field:

    Count ({vwApptDetail.Patient_Number}, {vwApptDetail.Resource_Abbr}) with some tweaks, other formulas or group select possibly, so I could use some kind of formula field in my crosstab that would give me the correct count for each doctor.

    This is a complex one for me to figure out.

    Any other suggestions are welcome!

    Add comment
    10|10000 characters needed characters exceeded