Summing a distinct count

[RenaG]

I'm using Crystal 10.

I need to generate a report that gives the average number of patients seen per day per clinic. I have a {distinctcount of (patID)} calculated for each apptDT (if a patient has multiple appointments on the same day he is counted once whereas, if he comes in on different days he has to be counted on each day). This appears in the apptDt group footer I have also created a {distinctcount of (apptDT)} to get the total number of days. This appears in the clinic group footer. How do I sum the {distinctcount of (patID)} so that I can calculate the average?

An example of what I have so far (just a partial view - last 3 days of the month):

(gf2-apptDt)    01/28/12          14    <-- this is distinctcount of (patID)
(gf2-apptDt)    01/29/12          121
(gf2-apptDt)    01/30/12          52
(gf1-clinic)                30          <-- this is distinctcount of (apptDT)

TIA!
~RLG

 

[RenaG]

I think I got it - or at least a good start. I have to take off now but will post my solution tomorrow.

TTFN!
~RLG

 

[MCuthill]

RenaG,

I think most simple approach is to run side-by-side RunningTotals, one incrementing each of your statistics. A final calculation "should" allow you to calculate the average by taking the Sum of you Distinct Counts (RunningTotal Variable 1) and divide it by the total number of appointment dates (Running Total Variable 2).

Please advise as to your success with your approach from your second post and we can go from there.

Cheers!

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."

 

[RenaG]

Ok, here is what I did. First I had to create a formula for the appt date because the field is a datetime field and I need to count just by date.

@fmtApptDtTm
Date({Schedule.App_DtTm})

Then I created several other formulas.

These two work perfectly.
This one I put in the group2 footer (apptdt) -
@SumDstnctPatID
WhilePrintingRecords;

numbervar SumOfDistinct := SumOfDistinct+DistinctCount({Schedule.Pat_ID1},{@fmtApptDtTm}, "daily")

This one I put in the group1 footer (Clinic) -
@DisplaySumPatID
WhilePrintingRecords;
numbervar SumOfDistinct;

All the following formulas or running totals went into the group1 footer.
Then I tried to calculate the number of days in the range I was running. In my test run, I used 1/1/12 - 1/31/12. This formula gave me a consistent 31 even if I didn't have appointments on all 31 days:

@CalcSumDstnctApptDt
WhilePrintingRecords;

numbervar SumOfApptDt := SumOfApptDt+DistinctCount({@fmtApptDtTm})

So I tried a running total which worked perfectly -
#CntDstnctDays
Field to summarize - @fmtApptDtTm
Type of summary - distinct count
Evaluate - For each record
Reset - On changing of group - clinic

Then I calculated the average patient count -
@CalcAvgPatPerDay
WhilePrintingRecords;

numbervar SumOfDistinct;
numbervar AvgPerDay := SumOfDistinct / {#CntDstnctDays}

To reset my calculations; this is put in the group1 header -
@ResetCalculations
WhilePrintingRecords;

I also tried a running total to count the distinct patient but couldn't get it to work; I tried every combination I could think of -
#CntDstnctPat
Field to summarize - Schedule.Pat_ID1
Type of summary - distinct count
Evaluate - For each record
Reset - On change of group - clinic

So I think I'm good. If anybody has any suggestions as to why the running total for the distinct patient count didn't work or the formula @CalcSumDstnctApptDt didn't work, I would be curious to know.

I hope somebody finds this helpful!

~RLG