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: Why is my first row of data not included when I get my chart from Excel
Viewed 2076 times since Fri, Nov 9, 2007
CR: CR1.1 - Notes and Causes Problem
Viewed 2049 times since Fri, Nov 9, 2007
CR: Chart [the name of the chart] failed with one or more errors
Viewed 2032 times since Fri, Nov 9, 2007
CR: Making an X-bar chart with a single data column
Viewed 2028 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2450 times since Fri, Nov 9, 2007
CR: Multiple chart lines from one data column
Viewed 2180 times since Fri, Nov 9, 2007
CR: General Filtering Tips
Viewed 2163 times since Fri, Nov 9, 2007
CR: Show every date on the chart
Viewed 2189 times since Thu, Jan 31, 2008
CR: Histogram bars beyond specification lines
Viewed 2267 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 1874 times since Fri, Jan 22, 2010