Home » Categories » CHARTrunner

CR: Charting pass-fail data as a p-chart

Revision Date: 2005-09-19

Problem:

Several customers have asked questions about making a p-chart from data in this general form:

SerialNumber

Date PassFail
1001 1/1/2003 PASS
1002 1/1/2003 PASS
1003 1/2/2003 PASS
1004 1/2/2003 FAIL
Etc    

Your data may contain many other columns. For this demonstration we’ll work with a simple table. As long as you have a column to establish some grouping (we’ll use Date) and another column containing the PassFail data, the solution below should work.

For a p-chart, you need to know the number inspected and the number of failures to calculate a percentage for each data point on the chart. In this example, it is not immediately apparent where we get the number inspected. Also, on some days we have zero that fail but this fact is not obvious just looking at the data. For example, notice that on 1/1/2003 there are no FAIL records.

We need a solution that will convert the data shown above into a form more suitable for a p-chart. Ideally, we will end up with a result table that looks something like this:

Date

Total FAIL PASS
1/1/2003 2 0 2
1/2/2003 2 1 1
Etc      

Solution:

If your data is in Excel you could use a pivot table to get this result. If you are creating a query, say in Microsoft Access, this is called a “transform” query. In either case, we are taking the PassFail column from the original data and turning this into multiple columns in the result data. In the example, we get a PASS column and a FAIL column.

In the attached .zip file, you will find an Access 2000 database and a CHARTrunner 1.6.x chart definition which demonstrate a solution to this problem. You will see that the chart is defined against a query that is stored in the Access database. Don’t be intimidated by this “transform” query. It looks complex, but it is only text that tell the database to do some work for us. It can easily be cut and pasted into your database and then altered to match your table and field names.

To study the solution to this problem, follow these steps:

Unzip the attached file into some temporary folder.
Run CHARTrunner 1.6.x or higher.
Set the CHARTrunner working folder to the folder you just created.
Take a look at the chart and the chart definition.
If you have Access 2000, you can look at the query by opening the Access database.

If you do not have Access 2000, here is the text of the query used to solve this problem:

TRANSFORM

iif(IsNull(count([pfdata].PassFail)),0,count([pfdata].PassFail)) AS CountOfPassFail

SELECT Min([pfdata].DATE) AS ProductionDate, Count([DATE]) AS Total
FROM pfdata
GROUP BY [pfdata].DATE
ORDER BY [pfdata].DATE
PIVOT [pfdata].PassFail;

Some things to note about this query:

The use of the IIF function (known as immediate if) is not standard SQL. This may not work for data in Oracle or other SQL databases. Without the IIF, the query will return an empty cell for days where there are NO failures. For a p-chart it is better to get an actual zero. To get a count for each date, we must use the count function and the group by clause. To get a separate column for PASS and FAIL, we use pivot.

Attachments (1) Attachments
Related Articles RSS Feed
CR: Charts not working on a new PC
Viewed 2049 times since Fri, Nov 9, 2007
CR: Bar Chart of a column of numbers in various categories
Viewed 1999 times since Fri, Nov 9, 2007
CR: CHARTrunner and Open Office
Viewed 2431 times since Fri, Nov 9, 2007
Cr: Charting elapsed time
Viewed 2483 times since Fri, Nov 9, 2007
CR: Show every date on the chart
Viewed 2244 times since Thu, Jan 31, 2008
CR: Label your X-Axis with more than one identifier
Viewed 2565 times since Tue, Oct 6, 2009
CR: Making a chart transportable
Viewed 2148 times since Fri, Nov 9, 2007
CR: Charting counted incidents
Viewed 2031 times since Thu, Feb 23, 2012
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2115 times since Fri, Nov 9, 2007
CR: Data grouping tardiness
Viewed 3247 times since Tue, Oct 6, 2009