Calculate defective unit repair rate

[elsenorjose]

Hello again everyone. Seems like lately I've been stuck with some real toughies, at least tough for me anyway

I am trying to calculate a metric involving repair rates for defective units for a certification report. Basically, the metric is defined thus:

If the unit is under warranty and it was returned (as tracked by a received_date field in a table) before end of the warranty period (as tracked by the expiration_date of the warranty in a table), then count it.

If the unit was returned (again, tracked by received_date) more than once and the latest received_date is within 6 months of the date the unit was sent back to the customer (based on a ship_date), count it.

Basically, the first part of the logic is just looking for anything that came back while still under warranty and I believe I've figured out that part:

If Not IsNull({RMA_Detail.SERIAL_52}) 
and ({RMA_Detail.RECDTE_52} <= {Ship_History.LABDTE_51} or {RMA_Detail.RECDTE_52} <= {Ship_History.MATDTE_51}) Then 1 Else 0

The second part, the one I'm having difficulty with, is saying if a part has been returned more than once (the serial number will exist more than once in the RMA_Detail table), we want to see if it was most recently returned within 6 months of having been repaired and shipped back to the customer, regardless of warranty status. So if the item has a latest ship date of 06/01/2011 and a latest received date of say, 08/01/2011, we'd want to count it because it was returned to us within 6 months of the last time we shipped it to the customer.

I guess I also need to be careful not to double count anything that is still under warranty that has been returned to us more than once, but I think my first piece of logic handles that.

I'm using CR XI R2 on a SQL Server 2005 backend.

Thank you.

 

[Madawc]

Are the details in one record or several?

If it is several, you need to do something with the group. One method is to create a formula that will be 1 or 0 depending on a condition. You can do a summary count for the group based on such a field, which has advantages over a running total. Something like:

If isnull({your.date}) then 1
else 0

It should be easy enough to adapt this method for your needs, showing relevant details in the group header or footer. TO develop it can be useful to display counts and working fields that you can then remove for the final report.

Crystal also has a function called DistinctCount that lets you count distinct occurances of values like account number while ignoring multiple instances of the same account number. But I doubt you'll need it.

The use of Crystal's automated totals is outlined at FAQ767-6524.

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

 

[lbass]

What is the warranty date field? The fields you are showing look like shipping dates--what do they refer to?

I think you should use a formula like this:
whileprintingrecords;
numbervar cnt;
if {table.receivedate} < ={table.warrantyenddate} or
(
count({RMA_Detail.SERIAL_52},{RMA_Detail.SERIAL_52}) >= 2 and
{table.receivedate} = maximum({table.receivedate},{RMA_Detail.SERIAL_52}) and
{table.receivedate}-previous({table.receivedate}) < 60
) then
cnt := cnt + 1;

Insert a group on serial_52 and then add the above formula to the detail section. Add a reset formula to the group header:

whileprintingrecords;
numbervar cnt;
if not inrepeatedgroupheader then
cnt := 0;

Add a formula to the display the results to the group footer:
whileprintingrecords;
numbervar cnt;

-LB

 

[elsenorjose]

Hi LB,

The warranty dates are the LABDTE_51 and MATDTE_52 fields (Lab = Labor and Mat = Materials).

I'll try your formulas and let you know how things out.

Thank you.

 

[elsenorjose]

LB,

I get an error stating "A summary has been specified on a non-recurring field. DetailsWarrantyDefect.

I modified the formula slightly since we no longer need the first part (Returned under warranty) and only need the part about returns within 6 months:

whileprintingrecords;
numbervar cnt;
if
(
count({RMA_Detail.SERIAL_52},{RMA_Detail.SERIAL_52}) >= 2 and {Ship_History.SHPDTE_51} = maximum({Ship_History.SHPDTE_51},{RMA_Detail.SERIAL_52})
and datediff("m", {Ship_History.SHPDTE_51}, previous({RMA_Detail.RECDTE_52})) <= 6
)
then
cnt := cnt + 1;

I kept the other formulas intact and did as you said re: grouping, using a reset field and putting the display formula in the group footer.

 

[elsenorjose]

Some more clarification from the requester. Looks like the logic has actually changed a bit.

I guess the clarification actually makes our report logic less complex. Here is my thought:
Defective units: IF the RMA SN appears in RMA database more than once AND datediff("m", Last_Ship_Date, Rec'd_Date) <= 6 THEN count ==> This is applied to the RMA SNs received in the month

All units: IF datediff("m", Last_Ship_Date, First_Date_of_the_Month) <= 6 THEN count ==> This is applied to all RMA SNs in RMA database

I would need these 2 totals in the report footer.

Thanks everyone for your assistance.

 

[lbass]

What is the serial_52 field? I see that it can be null. I thought it was related to the product being evaluated.

Can't follow your last post, as I don't know your lingo.

-LB

 

[elsenorjose]

Sorry, the SERIAL_52 field is the serial number. And I've confirmed that both the ship date (the date we shipped the unit to the customer) and the received date (the date we received the unit from the customer) could be NULL. It's possible that we never shipped a replacement and instead gave the customer credit, and it's possible that we never received a defective unit from the customer either.

The requester is basically asking for 2 counts. The first is for defective units: If a unit, identified by its serial number, appears in the RMA (Return Merchandise Authorization) table more than once AND the difference in months between the last time we shipped the unit to the customer and the last time we received it is less than 6 months, we count it as a defective unit. So, if we received it for repair on January 15, 2011 and we shipped it to the customer on January 20, 2011 after repairing it and it comes back to us any time before January 20, 2011 + 6 months (not 180 days, it has to be months) then we count it as defective. That's referenced in the pseudocode:

IF the RMA SN appears in RMA table more than once AND datediff("m", Last_Ship_Date, Last_Rec'd_Date) <= 6 THEN count

The second count is the total number of units which were returned for warranty repair, regardless of how many times, within the last 6 months as calculated from the first day of the month that the report is generated for. That's referenced in this pseudocode:

IF datediff("m", Last_Ship_Date, First_Date_of_the_Month_of_Report) <= 6 THEN count

We should have more of the 2nd count since it's not restricting the data by a count of serial numbers >=2.

I hope that helps clarify a bit more. This has been a tough one to wrap my head around and I'm under a tight deadline so the stress is making it even harder to think

Thanks again.

 

[lbass]

If you are able to group on seria_52, then try going to file->report optons->convert nulls to default values and then see if the formulas work. You should be able to adapt what I showed before.

-LB