Your question, your audience. Choose who sees your identity—and your question—with question security.

Hello,

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))

,day(dstart))

,dend),0);

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

Thanks

I have a report that includes a "Time in Program" total using the formula below:

Local DateTimeVar dstart := {collection_date.start_dat

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))

,day(dstart))

,dend),0);

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

Thanks

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Step by step would be very helpful.

thanks!

Then a new formula to calculate the average.

mlmcc

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_da

James

if isNULL({collection_date.en

then {?ToDate}

else if {collection_date.end_date}

then {?ToDate}

else if {collection_date.end_date}

then {collection_date.end_date}

else if {collection_date.end_date}

then {?ToDate}

else {?ToDate}

James

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?

James

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.

Thanks!

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.

James

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.

If the report looks OK otherwise, I'd have to guess that you just have multiple records per person. But if you really don't think that you should have multiple records per person, you might want to double check the tables that you're using and how they're linked. Also, if the tables that you're using just happen to pull in multiple records for each person and you don't actually need anything from all of those records, but there's no easy way to get rid of them using the links or a record selection, you could go to File > "Report Options" and check the "Select Distinct Records" option. Under the right circumstances, that can be an easy way to eliminate duplicates. Just make sure that it doesn't eliminate any records that you actually need.

Anyway ...

Create a formula like the following (call it whatever you like):

DateDiff ("d", {collection_date.start_dat

All that does is output the number of days between your dates.

Expand your detail section temporarily and drop that formula in there. Right-click on that formula and select Insert > "Running Total". Change the running total name if you like (this is going to be the total number of days in program for group 1). The "Type of summary" is Sum. Under Evaluate, select "On change of group" and select your group 4 from the dropdown list. That means that the result of that formula will only be added once for each group 4 (each person), rather than once for each record read for each person. Under Reset, select "On change of group" and select your group 1. That means that this running total will give you a total for each group 1. When you click OK, CR will put a running total field on the report. Move it to GF1, if it's not already there.

That should give you a total of the number of days in program in group 1.

Right-click the formula in the detail section again and select Insert > "Running Total" again. Change the running total name if you like (this is going to be a count of the people in group 1). The "Type of summary" is Count (Not "Distinct Count". Just Count). The rest is just like before. Under Evaluate, select "On change of group" and select your group 4 from the dropdown list. Under Reset, select "On change of group" and select your group 1. When you click OK, CR will put a running total field on the report. Move it to GF1, if it's not already there.

That should give you a count of the people (group 4's) in group 1.

To get your average, expressed as years, months and days, create a formula like the following (call it whatever you like) and put it in GF1.

Local NumberVar avg;

Local NumberVar yr;

Local NumberVar mh;

Local NumberVar dy;

avg := {#first running total above} / {#second running total above};

yr := Truncate (avg / 365.25);

mh := Truncate ((avg - (yr * 365.25)) / 30.5);

dy := avg - (yr * 365.25) - (mh * 30.5);

CStr (yr, 0) + "y " + CStr (mh, 0) + "m " + CStr (dy, 0) + "d"

Like I said before, I'm not really sure how to average a number of years, months and days. What that formula does is get an average number of days and divide that by 365.25 to get the years, and divide the remainder by 30.5 to get the months, and what's left is the days. If you'd rather use 365 days for a year or 30 days for a month, just change all of the references to 365.25 or 30.5 accordingly.

If that gives you what you're looking for, you'd calculate the group 2 and 3 averages in the same way.

Add running totals for the number of days and the count that are reset "on change of group" 2 and 3, and create new formulas for GF2 and GF3 that calculate the average from those running totals.

Once you're finished, you can delete the formula from the detail section if you like (and shrink the section again). I only had you put the formula there to make it easier to create the running totals. Once they've been created, that formula doesn't need to be there anymore. You can also delete the various running total fields that were added to the report if you like. If you delete those fields from the report, the running totals will still be there for the average formulas to use. They just won't be shown on the report.

James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

Do not forget to reset the variable value after.

P.