We help IT Professionals succeed at work.

# Average Calculation Based on Distinct Month Records per Person - Crystal Reports

on
Medium Priority
686 Views
Hello Experts,

I am trying to calculate the average number of months (Jan, Feb, etc.) a person has in their records based on the following:
Each person has several records and an associated datetime field.  I am interested in adding the DISTINCT number of MONTHS per person, and then dividing that number by the total number of people.
The various formulas and running totals I've tried so far are not correct.  Can you suggest a method for getting the average number of months a person has in their records per all the people in the report?

Thank you,
Chopp
Comment
Watch Question

## View Solutions Only

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try this

Build the report based on a COMMAND

Command will be

SELECT Distinct PersonName, Month(DateField) as dtMonth
FROM Yourtable

In the report create a formula
Count ({Command.dtMonth}) / DistinctCount ({Command.PersonName})

mlmcc

Commented:
What do you mean by 'COMMAND'?
I attempted to type your idea into the Record Selection, but there is obviously more to it.
Can you give me more instructions for how to try your idea?

~Chopp
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Create a new report.
When you add the database there should be a COMMAND option

mlmcc

Commented:
Terrific ! I never used a command before.  Thank you for introducing me to the option.
Can I link a command into the data model of another report ?  Or, link it to another report in a subreport?
I didn't see the command I created in the list of tables or stored procedures, etc.

~Chopp
Commented:
Create a new formula to change your datetime field to the 1st of each month i.e.

//@FirstDayofMonth
Date(Year({Your_Date_Field}), Month({Your_Date_Field}), 01)

You will need to have a group in your report and insert a Distinct Count summary field on the calculation. Youi can then use this in a new formula to accumulate the count of each person

//@CountAccumulate - this needs to be placed in the person group header section
Shared NumberVar Total := Total + DistinctCount ({@FirstDayofMonth}, {Person.Person_ID})

Then you can create a final formula to create your average

//@FinalAverage - place this in the report footer section
{@CountAccumulate} / DistinctCount ({Person.Person_ID})

HTH
CERTIFIED EXPERT
Commented:
I don't normally use Commands, but I think they're just stored in the report that they're created for (as opposed to a view or stored procedure, which is stored on the db server).

FWIW, going back to your original post, when you said "dividing that number by the total number of people", did you mean a count of _all_ of the people that are included in some table, or the total number of people that are included on the report (based on some criteria, like people with some activity during a certain period of time)?

If it was the latter, it seems simple enough.  You'd set the record selection to include the people with activity during the desired period of time and use a formula like one of the following:

I assume that you have some kind of "person ID field".  Is it string or numeric?

If it's a string:

DistinctCount ({person ID field} + " " + CStr ({datetime field}, "MMyyyy")) /
DistinctCount ({person ID field})

If it's numeric (specifically, an integer):

DistinctCount (CStr ({person ID field}, "#") + " " + CStr ({datetime field}, "MMyyyy")) /
DistinctCount ({person ID field})

The idea is to combine the "person ID field" and the month in one string and then do a distinct count on that.  I included the year (yyyy) with the month in case your report includes data from the same month in multiple years.  If it does and you only use the month, dates in the same month, but different years, would be counted as one month.  If your report will never include the same month in different years, then you don't have to include the year, but it won't do any harm either.

James
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
I believe you can store commands in the repository if you are using Crystal Server

mlmcc

Commented:
Hello Experts,

Thank you for all those ideas and the introduction to the command option.  I will try the @count accumulate idea in a future report.  I tried the formula suggested by James, which is the same method I tried before sending you my question.  I get the following error message using that formula: 'A field is required here'.  Do you have a way to resolve that error?  I will leave this question open for now, in case you do.

In the meantime, I ended up creating SQL views to handle this report.  I pasted the two scripts I am using below in case they are of interest to anyone.

Thank you again for your help.
~Chopp

View #1 Script --

Create View [dbo].[Vw_ClinTranSessionCountWithReferredByTemplate]
As
Select      Distinct
p.Patient_ID
,     p.Episode_ID
,     Sum(ClinTranCount_ClientsWithinFY.NumberOfSessions) As NumberOfSessions
,     Max(ClinTranCount_ClientsWithinFY.MaxProcChron) As MaxProcChron
,     p.fname As FirstName
,     SubString(p.fname, 1, 1) As FirstInitial
,     p.lname As LastName
,     p.dob As DOB

From dbo.Patient p (NoLock)
Join
(
Select      ct.Patient_ID
,     ct.Patient_Assignment_ID
,     Count(*) As NumberOfSessions
,     Max(ct.Proc_Chron) As MaxProcChron

From dbo.Patient_Clin_Tran ct (NoLock)
Join (     -- Only clients with at least one session in the FY
Select      pct2.Patient_ID
,     pct2.Patient_Assignment_ID
From PsychLIVE.dbo.Patient_Clin_Tran As pct2 With (Nolock)
Where       pct2.proc_chron >= '7/1/2010'
And         pct2.proc_chron <  '7/1/2011'
Group By    pct2.Patient_ID
,     pct2.Patient_Assignment_ID
) As ClientsWithinFY
On    ClientsWithinFY.Patient_ID = ct.Patient_ID
And ClientsWithinFY.Patient_Assignment_ID = ct.Patient_Assignment_ID

Join dbo.Patient_Assignment_Custom As pac With (Nolock)
On    pac.Patient_Assignment_ID = ClientsWithinFY.Patient_Assignment_ID

Join dbo.Vw_DropDownCodes_Referred As ref With (Nolock)
On    ref.field_value = pac.referred_by

Where
ct.Status = 'CO'
And         ct.proc_code In ('SCHLCONF','PARENTX','COLLATMTG','INTAKE','CASECONF',
'CASECLNMTG','CHILDTX','GRPTX','PROCONSULT','SCHLVISIT',
'CLNWRKSHOP','CLNPHONE','FAMILYTX','CRISIS','TEACHER',
And         ct.Service_ID = 'YTHSERVCTR'  --'CASEMNGMT' –‘DELINQUENCYPREVENTION’
And (ct.program_id = '602PROB' Or ref.field_description Like '%probation%')
And ct.clinician_ID <> '00000007'

Group By    ct.Patient_ID
,     ct.Patient_Assignment_ID

) As ClinTranCount_ClientsWithinFY
On    p.Patient_ID = ClinTranCount_ClientsWithinFY.Patient_ID

Where 1=1

Group By    p.Patient_ID
,     p.Episode_ID
,     p.fname
,     SubString(p.fname, 1, 1)
,     p.lname
,     p.dob

View #2 Script --

ALTER View [dbo].[Vw_SessionDurationMonthsDaysWithReferredByTemplate]
AS

Select      pct.Patient_ID
,     pct.Episode_ID
,     Min(proc_chron) As MinAppt
,     Max(proc_chron) As MaxAppt
,     Case
When Min(proc_chron) = Max(proc_chron) Then 1
When DateDiff(Hour, Min(proc_chron), Max(proc_chron)) < 24 Then 1
Else DateDiff(Day, Min(proc_chron), Max(proc_chron))
End As DurationInDays
,     Case
When Min(proc_chron) = Max(proc_chron) Then 1
When  DatePart(Month,Min(proc_chron)) = DatePart(Month,Max(proc_chron))
And DatePart(Year,Min(proc_chron)) = DatePart(Year,Max(proc_chron))      Then 1
Else DateDiff(Month, Min(proc_chron), Max(proc_chron)) + 1  --added the plus one in order to include each unique month even if the difference is only one month
End  As DurationInMonths

From dbo.Patient_Clin_Tran As pct With (Nolock)
Join (     -- Only clients with at least one session in the FY
Select      pct2.Patient_ID
,     pct2.Patient_Assignment_ID
From dbo.Patient_Clin_Tran As pct2 With (Nolock)
Where       pct2.proc_chron >= '7/1/2010'
And         pct2.proc_chron <  '7/1/2011'
Group By    pct2.Patient_ID
,     pct2.Patient_Assignment_ID
) As ClientsWithinFY
On    ClientsWithinFY.Patient_ID = pct.Patient_ID
And ClientsWithinFY.Patient_Assignment_ID = pct.Patient_Assignment_ID

Join dbo.Patient_Assignment_Custom As pac With (Nolock)
On    pac.Patient_Assignment_ID = ClientsWithinFY.Patient_Assignment_ID

Join dbo.Vw_DropDownCodes_Referred As ref With (Nolock)
On    ref.field_value = pac.referred_by

Where pct.proc_code in ('SCHLCONF','PARENTX','COLLATMTG','INTAKE','CASECONF',
'CASECLNMTG','CHILDTX','GRPTX','PROCONSULT','SCHLVISIT',
'CLNWRKSHOP','CLNPHONE','FAMILYTX','CRISIS','TEACHER',
And pct.[status] = 'CO'
And pct.service_id = 'YTHSERVCTR'
And (pct.program_id = '602PROB' Or ref.field_description Like '%probation%')

And pct.clinician_ID <> '00000007'
And pct.patient_id not in ('00000001', '00002208')

Group By pct.Patient_ID
,     pct.Episode_ID
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
What was the formula that gave the error?

mlmcc

Commented:
The formula was:

DistinctCount ({Patient_Clin_Tran.patient_id} + " " + CStr({Patient_Clin_Tran.proc_chron}, "MMyyyy")) /
DistinctCount ({Patient_Clin_Tran.patient_id})

‘A field is required here.’

~Chopp
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
The first DistinctCount is missing a )

DistinctCount ({Patient_Clin_Tran.patient_id}) + " " + CStr({Patient_Clin_Tran.proc_chron}, "MMyyyy")) /
DistinctCount ({Patient_Clin_Tran.patient_id})

mlmcc

Commented:
Thank you for noticing the missing parenthesis.
The formula still throws an error - requiring a number where the quotes are.  I am moving on from this method.

Thank you for all the great ideas.
~Chopp
CERTIFIED EXPERT

Commented:
Ah.  One of the eccentricities of CR.  mlmcc was wrong about the ")".  The problem is that CR won't let you do a count on a combination of fields like that.  However, there is a simple way around that.  Put the combination of fields in a formula, and then do a count on that.

Create a formula like this:

{Patient_Clin_Tran.patient_id} + " " + CStr({Patient_Clin_Tran.proc_chron},  "MMyyyy")

Then calculate your average in a formula like this

DistinctCount ({@formula created above}) / DistinctCount ({Patient_Clin_Tran.patient_id})

That should not produce any errors.

James
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
AGree.  I didn' interpret the requirement correctly if it is to divide the counts.

mlmcc