Home » Categories » CHARTrunner

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

Revision Date: 2005-09-19


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


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

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:


1/1/2003 2 0 2
1/2/2003 2 1 1


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:


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: Making an X-bar chart with a single data column
Viewed 2160 times since Fri, Nov 9, 2007
CR: Charts not working on a new PC
Viewed 2118 times since Fri, Nov 9, 2007
CR: Installing CHARTrunner disables desktop wallpaper
Viewed 2153 times since Fri, Nov 9, 2007
CR: Multiple chart lines from one data column
Viewed 2357 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2656 times since Fri, Nov 9, 2007
CR: Histogram bars beyond specification lines
Viewed 2404 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 2045 times since Fri, Jan 22, 2010
CR: CR1.5 - My control chart data lines are not printing
Viewed 2262 times since Fri, Nov 9, 2007
CR: CR1.1 - Notes and Causes Problem
Viewed 2166 times since Fri, Nov 9, 2007
CR: Show every date on the chart
Viewed 2301 times since Thu, Jan 31, 2008