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

Do not forget to reset the variable value after.

P.

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

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

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

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.