Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Thant (TechnicalUser)
5 Aug 10 9:07
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

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 (Programmer)
5 Aug 10 9:36
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 (Programmer)
5 Aug 10 10:27
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: Basics of totals.

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.

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

fisheromacse (IS/IT--Management)
5 Aug 10 10:41
"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 (TechnicalUser)
11 Aug 10 9:46
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 (Programmer)
11 Aug 10 10:25
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 (TechnicalUser)
13 Aug 10 8:47
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

CODE

=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

CODE

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


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

CODE

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

CODE

=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!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close