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
Anything worth doing is worth messing up at least 5 times before you get it right!
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!