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: Show every date on the chart
Viewed 2217 times since Thu, Jan 31, 2008
CR: SQL Server Stored Procedures Returns No Records
Viewed 2269 times since Fri, Nov 9, 2007
CR: Data grouping tardiness
Viewed 3161 times since Tue, Oct 6, 2009
CR: Charts with large amounts of data
Viewed 1914 times since Fri, Nov 9, 2007
CR: Excel tips
Viewed 3020 times since Tue, Oct 6, 2009
CR: General Filtering Tips
Viewed 2206 times since Fri, Nov 9, 2007
CR: CR1.5 - My control chart data lines are not printing
Viewed 2183 times since Fri, Nov 9, 2007
CR: Charts not working on a new PC
Viewed 2000 times since Fri, Nov 9, 2007
CR: Chart [the name of the chart] failed with one or more errors
Viewed 2068 times since Fri, Nov 9, 2007
CR: Installing CHARTrunner disables desktop wallpaper
Viewed 2059 times since Fri, Nov 9, 2007