Formula for finding 'age of oldest' call data

[Monkeyboy126]

Difficult one to explain this but I'll have a go....

I run a technical call centre and have a Problem and Service Request database in SQL. Data includes open date, close date etc and I have been asked to produce a report in Crystal at the end of each month that shows the age of the oldest open call on a week by week basis (every Friday) for the period covered.

So the report would display a week ending date for the month and show the age of the oldest call that was open at that time (but may be closed by the end of the month) if you see what I mean....

Any ideas would be great.

Thanks

 

[Naith]

Hey MonkeyBoy,

What would be really great here would be if you could give us an example of what you want the finished report to look like, compared with a little excerpt of the data you've currently got to work with.

Naith

 

[Monkeyboy126]

Naith

The report is usually (but not always) run at month end. It is currently compiled in Excel manually by noting the oldest open (or active) call in the database at the end of play each Friday. So you have a weekending date (which is Friday) and below the age of the oldest acive call (in days). Then the same for recurring week endings.

Does this help??

 

[kenhamady]

See if this makes sense.

You create a formula that returns the Friday of the call week:

{CallDate} - DayOfWeek ({CallDate}) + 6

Then you group on this formula to get weekly groups with a week ending date.

Then you write a formula that says:

If IsNull ({closeDate}) or {closeDate} > {@WeekEndDate}
then {@WeekEndDate} - {closeDate}
else 0

This gives you the age on that Friday for calls opened that week.

Then you sort these so that the highest age is last. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Monkeyboy126]

Ken

You've lived upto your reputation again...

Problem solved !!!

Many thanks

 

[Monkeyboy126]

Ken

Although........

Am I correct in thinking that this will not take into account any calls that are still in an open state, i.e there is not yet a close date?

Paul

 

[rosemaryl]

Dear Monkeybo126,

Hey, how are you? I was wondering if you could post a little data example of what you wanted this too look like. This is intriguing.

By the way, for any call that you want to determine aging right now, use whatever formula you normally use and replace the date field with a date variable:

//ex beg
DateVar d := If {Incident.State:} = "O" then CurrentdateTime else {Incident.Close Date & Time};

datedif("d", {Incident.Open Date & Time}, d)

//for example

Obviously you will have to change the logic for your Call status field and call close date field, but I use this all the time. So you would get the age of the call right now regardless of whether it is open or closed.

Please post data for the original question because I am trying to visulize. By the way, did that Stored Procedure solve your other problem long ago?

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Monkeyboy126]

Rosemary

Yes the Stored Procedure worked a treat thanks...

I'll send you via email the report that I need to complete which should give you some idea of what I need. Unfortunately Ken's formula didn't work as first thought.

Many thanks

Paul

 

[kenhamady]

Probably because my formula has an error.
To get the age you don't use:

{@WeekEndDate} - {closeDate}

you would use:

{@WeekEndDate} - {OPENDate}

Using that, the formula takes into account records that some are yet to close. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Monkeyboy126]

Ken

That now works but.... on a week by week basis. How would I obtain a cumulative figure over the weeks?

The formula shows the oldest call upto Friday but is not included in the following week if it remains in an open state.

Does that make sense?

 

[kenhamady]

If you are trying to show the same call in 2 different groups, then you are going to have to create a variable and logic to pass the value forward. Are you familiar with CR variables? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com