formula for average days taken to close enquiry 1

[javedi]

Hello

Does anyone know how I can work out the averga enumber of days it has taken to close an enquiry.
The {@enqClosedDate} is a formula to extract a date.

I am using this as formula but crytsal does not like this.
Average ({Enquiry.ReceivedDate},{@enqClosedDate})

I'm using crystal report 10.

Any help welcomed.

Thanks,
Javedi

 

[lbass]

Please show the content of the date formula. Are you trying to show the average difference between received date and closed date? Is this for a particular group? Is so, what group?

-LB

 

[Tatertot45]

Try using DateDiff ("d", Startdate, Enddate) to find the number of days between two dates. Then you can use Average based on your groupings.

 

[javedi]

Hi

Tatertot45's formula worked fine.

lbass the formula is;

//{@enqClosedDate}
if mid ({NoticeAudit.EventDescription},17,10) ={Enquiry.EnquiryNo} then
date ({NoticeAudit.NoticeEventDateTime})
else
date(2020,09,09)

Thanks
Javedi

 

[lbass]

I think you will get skewed results, since the difference will be large whenever your criterion for enquiryno is not met. I would instead create these formulas:

//{@null}:
Open and save a new formula without entering anything.

//{@ReceivedDate}:
if mid ({NoticeAudit.EventDescription},17,10) ={Enquiry.EnquiryNo} then
{Enquiry.ReceivedDate} else
date({@null})

//{@enqClosedDate}
if mid ({NoticeAudit.EventDescription},17,10) = Enquiry.EnquiryNo} then
date ({NoticeAudit.NoticeEventDateTime}) else
date({@null})

//{@diff}:
datediff("d",{@ReceivedDate},{enqClosedDate})

//{@Average}:
Average({@diff},{table.group})//if there is no group, remove the second condition

-LB