Control Charts Alarm

[#1]

Hi,

Kind of a newbie with JMP Scripting. Appreciate any help that I can get.

Here are the steps that I'm trying to achieve. First I'm opening the excel file and within that excel file selecting a sheet.  Then I'm applying few SQL statements to get a subset of the sheet;s data and naming that data set

as Subset of Adherence.  Following that is creation of log to which I'm writing all the control points which have failed. Next is to chart out a control chart based on the Final CPASU column. Here is where my problem begins.

I want the control chart to be drawn only if the data has failed any of the tests. In other words if the data points pass all the seven tests of control limits I don't want JMP to display that chart. It should only display the chart when something is out of limits.  This is particularly useful when I have more than 50 charts which is the case here but I have not included all the other 49 charts code.

Doing that would ensure I'm only focusing on correcting things that are going wrong than looking at all charts a

nd figuring out which charts data points went wrong.  Finally what I'm doing is saving these charts as a HTML file in a folder so I can write a script in VBA which would then post these images on sharepoint site.  This step is working fine but I'm falling short of ideas with the above step.

My code follows:

                    Open Database("DSN=Excel Files;DBQ=C:\Users\XXXXX\Documents\XXXX\JMP Analysis\XXXXX.xlsm;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", "SELECT  * FROM `'CPASU$'`","CPASU$");

               //Create the subset

          (Data Table( "CPASU by queue$" ) << Select Where(AND(FWEEK>=201201,FWEEK!=201248, FWEEK!=201247, FWEEK!=201243, Adherence>0))<< Subset( Linked ) ;

          Current Data Table( Data Table( "Subset of Adherence $" ) ));

          //Clearing the log and setup columns for csv file

          Clear log();

          Write("Queue Name,Test,Column,Data Point\!N");

               //Begin Control Charts

          New Window( "Subset of CPASU",

          V List Box(

          Control Chart(Sample Label( :FWEEK ),(Alarm Script(Write("NA Standard,",qc_test,",",qc_col,qc_sample,"\!N")),

          Group Size( 1 ),

          KSigma( 3 ),}

          Chart Col( :Final CPASU, Individual Measurement(Shade Zones(1), Test 1(1)) ),Where( :Queue Name == "NA Standard" ),),);

                    Savelog("C:\Users\XXXX\Documents\XXXX\JMP Analysis\JMPlogs.csv");

          Window(“Subset of CPASU”) << SaveHTML (“C:\Users\XXX\Documents\XXX\JMP Analysis\HTML\CPASU.html”);

Please ignore if there is a mismatch in the number of opening and closing brackets.

[#2]

Hey Ashwin,

     There are a few ways I can think to do this but I believe the easiest would be to script out your control charts as is and then have JMP open that log file you wrote as a table. From here there is are a few ways you can go but essentially you now have a table in JMP that shows which breakouts have failures. You can load a distinct list of values of "Queue Name" into a list variable and have that loop through a 2nd script like you have... essentially replacing where(:Queue Name = "NA Standards") with Where(:Queue Name = List) that way it will only script the charts that have failed. So your 2nd section would look something like:

V List Box(              

For( i= 1, i <= N Items(LISTNAME), i++,

          Control Chart(Sample Label( :FWEEK ),(Alarm Script(Write(char(LISTNAME,",",qc_test,",",qc_col,qc_sample,"\!N")),

          Group Size( 1 ),

          KSigma( 3 ),

          Chart Col( :Final CPASU, Individual Measurement(Shade Zones(1), Test 1(1)) ),Where( :Queue Name == LISTNAME ),),));

Naturally replacing the LISTNAME with whatever you want to call you list. I haven't tested this code so there might be some tweaking for parenthesis and commas... but I think you get the idea.