Solved

# Crystal Report Average "Time in Program"

Posted on 2009-07-06

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