Crystal Reports 10 - How to Calculate Process Sigma

[kwtx04rm]

Does anyone know if Crystal Reports 10 (Professional) has a function to calculate process sigma? BTW, the database is SQL Server 2000.

The Crystal Report already returns # of opportunities and # of defects. I just need a Crystal Report function or formula to pass two variables to (#opps & #defects)and have the function/formula generate the process sigma.

Example:
Opportunities are 351 - result from Crystal Report
Defects are 51 - result from Crystal Report
Process Sigma is 2.56 - since I'm currently unable to generate this result from Crystal Report, I used Sigma Calculator from

http://www.isixsigma.com/sixsigma/six_sigma_calculator.asp

Any help is greatly appreciated!

 

[CR85user]

There is a formula located on the same site which lists the properties of the calculation:

Understanding The Formula
Defects Per Million Opportunities (DPMO) = ((Total Defects) / (Total Opportunities)) * 1,000,000
Defects (%) = ((Total Defects) / (Total Opportunities)) * 100
Yield (%) = 100 - (Defects Percentage)
process Sigma = NORMSINV(1-((Total Defects) / (Total Opportunities))) + 1.5

Alternatively,
process Sigma = 0.8406 + SQRT(29.37 - 2.221 * (ln(DPMO))).


You can create formulae to get most of the figures needed.

For example if you have two fields:

{database.opportunities}
{database.defects}

If you then create the following formulas:

{@DPMO}
//Defects Per Million Oppotunities
({database.defects}/{database.opportunities}) * 1000000

{@defect%)
//Percentile of defects
({database.defects}/{database.opportunities}) * 100

{@yield%}
//Percentile of yield
100 - {@defect%}

{@Sigma Shift}
//Add sigma shift here to adjust easily
1.5


The only prob I saw then was how you perform the NORMSINV function.

Anyone else have an idea?

'J

 

[kwtx04rm]

Thank you very much CR85user.

Your tips on creating the formulas worked. But unable to figure out how to use NORMSINV. So I used the alternative for process sigma, here is logic:

@Sev1 DPMO
//DPMPO for Service Restoral Sigma
(Sum({HierarchySummary.Sev1_Restoral_Defect_Count})/Sum({HierarchySummary.Sev1_Restored_Count})) * 1000000

@Sev1 Percent Defect
//Percentile of defects
(Sum({HierarchySummary.Sev1_Restoral_Defect_Count})/Sum({HierarchySummary.Sev1_Restored_Count})) * 100

@Sev1 Yield
//Percentile of yield
100 - {@Sev1 Percent Defect}

@Sev1 Sigma
//Alternative since unable to figure our NORMSINV...
0.8406 + Sqr(29.37 - 2.221 * (log({@Sev1 DPMO})))

Finally, the formula @Sev1 Sigma returned 2.5640.

Then a colleague pointed me to some code they use for calculating process sigma with NORMSINV for a web application. It appears they came up with a way to calculate NORMSINV with code. I used it in the Crystal Report and it returned 2.5568.

Here is the code:

Sqr(-2 * Log({@Sev1 DPMO} / 1000000)) - ((2.51557 + 0.802853 * Sqr(-2 * Log({@Sev1 DPMO} / 1000000)) + 0.010328 * Sqr(-2 * Log({@Sev1 DPMO} / 1000000)) ^ 2) / (1 + 1.432788 * Sqr(-2 * Log({@Sev1 DPMO} / 1000000)) + 0.189269 * Sqr(-2 * Log({@Sev1 DPMO} / 1000000)) ^ 2 + 0.001308 * Sqr(-2 * Log({@Sev1 DPMO} / 1000000)) ^ 3)) + 1.5

 

[CR85user]

Excellent stuff, Glad to see that you came back to share your code with the community also.

It is always appreciated and is the reason Tek Tips continues to thrive.

'J