## Calculating Turn Around Time

## Calculating Turn Around Time

(OP)

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

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

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

#### CODE

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

Thanks in advance

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

## RE: Calculating Turn Around Time

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.

## RE: Calculating Turn Around Time

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

## RE: Calculating Turn Around Time

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!

## RE: Calculating Turn Around Time

Thanks

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

## RE: Calculating Turn Around Time

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

Gary Parker

MIS Data Analyst

Manchester, England

## RE: Calculating Turn Around Time

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

## CODE

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

## CODE

and AVG turn around time with exceptions ( July = 4.9 days)

TAT W/Exceptions

## CODE

## CODE

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!