Calculating Turn Around Time

[Thant]

Greetings,
We have just had a consultant come in and help us try to caluclate our order turn around time from our mail order pharmacy. His solution has us copying things in and out of excel and takes a very long time (multiple hours) to process the sheets. I need to find a better way. I know crystal can perform the same functions and probably faster but I dont have the first clue as to how to accomplish it. Im including a cut section of his document telling us how to caluclate as well as some excel code he used in his sheets. Any help with this would be greatly appreciated

From consultants documents:
The current process for data extract utilizes Crystal Reports to extract data on a month by month basis for use in calculating TAT. There are two data extracts that are required. First, the SHIPMENTS table extract for orders that “FINISHED” during the month desired. (Confirm that the data extracted is the time period desired) Second, the SHIPMENT HISTORY is extracted to identify the queues that each order in the SHIPMENTS extract visited during the life of the order at CarolinaCare.
Data order in the extracts must be in the following order:
SHIPMENTS extract should contain (in this order):
Shipment_ID / Order_ID /Current_Area / Arrive_in_Current_Area / Original_Order_Date
Note that Original_Order_Date is from ORDERS table not SHIPMENTS table.
SHIPMENT HISTORY extract should contain (in this order):
Shipment_ID / Order_ID /Current_Area / Arrive_in_Current_Area / Original_Order_Date /Shipment_ID /Prod_Area
Note that the second occurrence of Shipment_ID is from SHIPMENT HISTORY along with PROD_AREA while the other data is from SHIPMENTS table. This ensures that the history will match the shipments from the SHIPMENTS extract.
The above extracts should be exported into Excel spreadsheets that will be used to copy data into TAT spreadsheets that will be described below.

(Note this process requires two seperate crystal reports to be run. Im pretty sure there is a way to combine these reports)

About the calcluations
DESCRIPTION OF CALCULATIONS
The sheets calculate the average TAT by determining the length of time that a given order spent in processing at CarolinaCare. This is determined by subtracting the date/time the order was created from the date/time that the order “finished”. The next consideration is whether an order went to an exception queue during the time in processing. The sheets use the “shipment history” to determine if an order spent any time in one of the exception queues and if so the order is marked as an exception. This set of formulas utilizes a feature of Excel called Array Functions. If any of the cells containing an array function are changed, Excel requires that they be saved using <CTL>,<SHIFT>,<ENTER> rather than the normal <ENTER>. If you view an array formula in a given cell you can tell that it is an array function by the presence of {} around the formula. Excel places the {} around the formula when saving that formula using the above method, <CTL>,<SHIFT>,<ENTER> . If you do not save the formula correctly you will get unintended results that will affect the accuracy of the calculated TAT!

We then take this data and put it into a summary sheet and it calculates

Here is some of his excel code

=SUM((ItemHistory!$A$1:$A$150000='Shipments Finished'!$A2)*(ItemHistory!$G$1:$G$150000=H$1))

Any ideas here would be appreciated. I think this one might be tricky but most of you are masters far better at programming than I am.

Thanks in advance

Anything worth doing is worth messing up at least 5 times before you get it right!

 

[NiceArms]

A lot of the forumlas that you can use in Excel can be used in crystal reports.

If you are new to cyrstal reports i'd suggest doing a little reading with the aim of gathering an understanding of the software, i.e. how to setup parameters, how to add formulas, how to add/remove tables, how to add links between tables, etc. (google should help you here)

linking of tables will be your starting point to joining the 2 reports into 1.

 

[Madawc]

In Crystal, DateDiff is the way to find the difference between two dates or times. Once you have this at detail-line level, Crystal lets you total it without the need to write your own formula. The use of Crystal's automated totals is outlined at FAQ767-6524.

It's also worth doing a few test reports to discover what Crystal can do, before trying to apply this knowledge to a real-world task.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[fisheromacse]

It's also worth doing a few test reports to discover what Crystal can do, before trying to apply this knowledge to a real-world task."

Great advice that i have very rarely been able to apply in the real world.
As is the case with most posters here, i would guess, many of the requests for something to be done come with the added phrase, "by yesterday if not sooner" hehehehehe!

 

[Thant]

The main thing I need to figure out is the array that he used. I ahve used crystal quite a bit but have never been confronted with this level of calculation and will admit to not being a programmer with a strong knowledge of formulas. I would like to get help figuring out how to make this formula in crystal and from there i can usually figure out why it worked.

Thanks


Anything worth doing is worth messing up at least 5 times before you get it right!

 

[GJParker]

I think it would be easier to help you if you posted an example of your data and the expected outcome for this.

If you can do this it may be easier to provide a solution.

Gary Parker
MIS Data Analyst
Manchester, England

 

[Thant]

Sorry for the delay in response.
As I understand it the process goes as follows

We pull out shipment data from our database in one month periods.

SHIPMENTS extract should contain (in this order):
Shipment_ID / Order_ID /Current_Area / Arrive_in_Current_Area / Original_Order_Date
Note that Original_Order_Date is from ORDERS table not SHIPMENTS table.

and
SHIPMENT HISTORY extract should contain (in this order):
Shipment_ID / Order_ID /Current_Area / Arrive_in_Current_Area / Original_Order_Date /Shipment_ID /Prod_Area

There are 12 possible queues(prod areas) that an order can travel thru they are
FINISH SHIP1 HOLD BALQ1 DCB1 FLD CLAIMS STEX1 SREX1 SRDC1 STECH1 CSETL and CAUTH
Everything after FLD CLAIMS is considered an "exception" queue (This becomes important later)
We process how long the orders remained in each queue by subtracting the date/time the order was created from the date/time the order finished

using data from our shipment history table we then calculate if an order spent any time in an "exception" queue.
This is done in the current excel sheet by an array

=SUM((ItemHistory!$A$1:$A$124000='Shipments Finished'!$A2)*(ItemHistory!$G$1:$G$124000=H$1))

We then take this sheet after it caluclates and copy it to a sumamry sheet which gives us an average
Once that average is caluclated it gives us two numbers
Avg turn around time per order ( In july that was 1.1 days)
This is the excel code
TAT No Exceptions

=AVERAGEIF($S$2:$S$30000,"N",$F$2:$F$30000)

and AVG turn around time with exceptions ( July = 4.9 days)
TAT W/Exceptions

=AVERAGEIF($S$2:S30000,"Y",$F$2:$F$30000)

And TAT ALL

=AVERAGE($F$2:$F$30000)

I hope this helps. Ive been wracking my brains trying to figure out how to do this but I cannot figure out the array (as ive never used them before).
If anyone needs any more information ill be more than happy to provide whatever I can.

I appreciate the help. Im getting thrown into the middle of this and I know that this current system (with its 4 hours of processing time) can be made better thru crystal somwhow.

Thanks

Anything worth doing is worth messing up at least 5 times before you get it right!