We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Crystal Report Average "Time in Program"

Medium Priority
Last Modified: 2012-05-07
I have a report that includes a "Time in Program" total using the formula below:

Local DateTimeVar dstart := {collection_date.start_date};
Local DateTimeVar dend:= {@isnull};
Local StringVar yy := '';
Local StringVar mm := '';
Local StringVar dd := '';

// years
if DateAdd ('yyyy', -(DateDiff ('yyyy',dstart ,dend )), dend) >= dstart
    then yy := totext(DateDiff ('yyyy',dstart ,dend ),0)
    else yy := totext(DateDiff ('yyyy',dstart ,dend )-1,0)

// months
if month(dend) = month(dstart)          // same month
    then (

if day(dend) >= day(dstart)
            then mm := '0'
            else mm := '11';

    else                                // diff month
        (if DateAdd ('m', -(DateDiff ('m',dstart ,dend )), dend) >= dstart
            then mm := totext(remainder(DateDiff ('m',dstart ,dend ),12),0)
            else mm := totext(remainder(DateDiff ('m',dstart ,dend ),12)-1,0);)

// days
if day(dend) < day(dstart)          //earlier
    then dd := totext(datediff('d'
                        ,dateserial(year(DateAdd ('m', -1, dend))
                                ,month(DateAdd ('m', -1, dend))
if day(dend) = day(dstart)          //same
    then dd := '0';
if day(dend) > day(dstart)          //later
    then dd := totext(day(dend) - day(dstart),0);

// print out with spacing - can be right or left justified
iif (len(yy) = 1, '  ', '')
& yy & 'y'
& iif (len(mm) = 1, '   ', ' ')
& mm & 'm'
& iif (len(dd) = 1, '   ', ' ')
& dd & 'd'

I was told to use this formula to calculate the years, months and days that a person was in a group.  I need to create a formula to average the "Time in Program" for each program. I currently have the report grouped by:
G1-State, G2-(parent) Program, G3-Program then G4-consumer.

I would like to have an average in G3, G2 and G1

Watch Question

Create a subreoprt that calculates total time for each consumer and store that value in a shared variable. Use the shared variable to calculate average with subtotals in each group.
Do not forget to reset the variable value after.
I'm not that familiar with these types of formulas. Can you elaborate on how to store the value in a shared variable and resetting it, please?

Step by step would be very helpful.

 Can you post the screenshot of the layout of your report? Preferable one for both the "Layout" and "Preview Tab"?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Do you get the correct values for the time in the group?

Here are the screen shots of the detail and preview screens:

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

You will need to modify the formula to do the addition of the time and count the records
Then a new formula to calculate the average.

Can you explain what you mean in detail please, maybe give me and example?

First of all, you appear to have an issue with your dates.  Your preview screenshot shows a couple of lines with 109 years in a program ,which seems unlikely.  :-)  The start date on those two lines shows 00 for the year, so the obvious guess is that the year should be 2000, but it's either actually 1900 instead, or it is being interpreted as such somewhere along the line.  That might be something that you need to look at.  Keep in mind that that inflated year count will affect your averages.

 As for your averages:

 The formula that you posted gets the ending date from a formula named {@isnull}.  Can you post that formula?

 The formula that you posted gets the starting date from a field ({collection_date.start_date}), but it looks like you have that formula in a group header (GH4), meaning that it's not evaluated for each record.  Is that correct?  If the total "time in program" is not a total of every record, that will affect how you calculate the total and how you produce the average (dividing a total from every record by a count of every record would not produce the correct results).

Here is the isnull formula

if isNULL({collection_date.end_date})
    then {?ToDate}

else if {collection_date.end_date} > {?ToDate}
    then {?ToDate}

else if {collection_date.end_date}< {?ToDate}
    then {collection_date.end_date}

else if {collection_date.end_date} = {?ToDate}
    then {?ToDate}

else {?ToDate}
I looked in our database and see that the count of years is correct and that in the database is where the error is.  It is a simple user error.  Input is year 1900 that will be fixed
AS far as the evaluation for each record, it is, it's evaluating each record for the consumer who is Group 4, I suppressed that feild for privacy reasons.

The formula is @DetailTimeProgram, correct?  You have that formula in GH4, which means that it will be evaluated once at the start of each new group 4, _not_ for every record.  If that's correct, then the count that's used to calculate the average also needs to be a count of the group 4's, as opposed to a count of each record.  If every record also happens to be a different group 4, then it's the same thing, but that wouldn't normally be the case.


Two things I forgot:

 The @isnull formula seems straightforward enough.  I don't think it will be an issue.  I just wanted to see what it was doing.

 How do you want the average to be displayed?  You're showing the "time in program" as a number of years, months and days.  Do you want the average in a similar form?  I'm thinking that that might be a bit tricky to do accurately.  Let's say that you get the difference between the dates in days and calculate an average of that.  How would you convert that to a number of years, months and days?  What's an "average" month?  30.5 days?

I don't know what you mean "each record" If I'm evaluating each consumers time in a group, then isn't that for each consumers record?

I would want the average to be in the same format that it is for time in program and average 3 of years, months, days.

This is the way the old report was that I'm redueing in the crystal format instead of sql.

I hope this can be done becuase this is what was requested.
I meant to say an average # of years, months and days-sorry

In CR, "records" refers to the individual records that CR reads from the datasource.  For example, if a report is reading invoice items (one record for each item on each invoice) and the report is grouped by invoice number, the invoice number would be the group and the items would be the records.  A formula that was placed in the invoice number group header would be evaluated once for each invoice, at the start of each new invoice.  A formula that was placed in the detail section would be evaluated once for each item on each invoice.

 Your formula is in GH4, so it's evaluated once for each "group 4", whatever group 4 is.  If the formula was in a detail section, it would be evaluated for every record within each group 4.  Which is correct really depends on your data.

 If the formula stays in GH4, we need to get a count of the "group 4"s, to calculate the average.
 If the formula is moved to a detail section, we need a count of the detail records, to calculate the average.

Ok, that's what I thought you were saying, yes that's correct then becuase Group 4 is The Consumer or Person.

So we would need to get a count of people in Group 4

Than use it in the average count of time in preogram for groups 3, 2 and 1

The reason I have the Person in Group four is because for some reason I am getting duplicates when it's in the detail section.
Unlock this solution and get a sample of our free trial.
(No credit card required)
Thanks James

You're welcome.  Glad I could help.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.