calculating using a formula field 1

[GCCkjn]

I have Crystal version 9. I've created a report to calculate the waiting time for a client since the initial assessment. Since there is sometimes more than 1 initial assessment for a client, I created a formula to capture the most recent i.e. maximum (initial assessment date). So now I want to calculate the time from the maximum(initial assessment date) to the current date. When I create this formula, currentdate - {@most recent initial ax}, it says no errors found but when I try to save it,I get an error message that says:
"A summary has been specified on a non-recurring field"

Can you help?

 

[Madawc]

You need to use DateDiff for dates. Try that, and if it still gives trouble, please post your exact formula.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You need to show the content of your formula.

-LB

 

[GCCkjn]

Here is the formula for the most recent assessment

Maximum ({vw_init_assessment_date.ActivityDateTime},{Pat.CPINo} )

I was trying to calculate the time from that date to today. These are the formula's I've tried:

1. currentdate - {@most recent initial ax}

2. DateDiff ("d", {@most recent initial ax}, currentdate)

3. DateDiff ("d", (Maximum ({vw_init_assessment_date.ActivityDateTime},{Pat.CPINo} )) , currentdate)

For each of these I get the same result: "A summary has been specified on a non-recurring field"

I wonder if I use a subreport, can I use the data in the subreport as part of a formula in the primary report?

 

[lbass]

I think you might have nulls in your activity datetime field. What result would you want the calculation to return in that case?

-LB

 

[GCCkjn]

I suppose there could be a null value although none are showing up that way in the report. Would it be able to return "n/a" in that case? or just left blank?

 

[lbass]

Create a formula {@IADate} like this:

if isnull({vw_init_assessment_date.ActivityDateTime}) then
date(0,0,0) else
{vw_init_assessment_date.ActivityDateTime}

Then use a formula like this for your calculation:

datediff("d",maximum({@IADate},{Pat.CPINo}),currentdate)

This assumes you have a group on {Pat.CPINo}.

-LB

 

[GCCkjn]

lbass, you are the best!! That works thanks. I love it when a report does what I want

Now, how can I average that wait time for all clients?

 

[lbass]

Please lay out all your requirements in the initial post.

Create two formulas:

//{@accum} to be placed in the patient group section containing your datediff formula ({@datediff}):

whileprintingrecords;
numbervar sumdiff := sumdiff + {@datediff};
numbervar cnt := cnt + 1;

Then in the report footer, use this formula:

whileprintingrecords;
numbervar sumdiff;
numbervar cnt;
sumdiff/cnt

-LB

 

[GCCkjn]

Thanks lbass. Sorry I didn't identify the average initially. I naively thought the averaging would be a simple thing to do once I had the wait time.