How to draw Control Charts that ignore missing data?

[#1]

Hi all. Sometimes we take a sample and test it for a number of parameters. On occasion, we don't need to test all parameters, so the data table can take the form of the layout below, whereby A or B can have missing values. When I draw control charts, the control chart leaves a space for the empty cells (per 1st chart below). Obviously, I'd much rather it looked like the 2nd chart below, whereby there are no 'gaps' on the Y axis. I know that the Control Chart is simply trying to maintain the time order, but for me this is unusable, especially as there could be many hundred of blank spaces present. Is there an easy way to do this?

I don't want to delete the rows where A is blank, as there may be data present for B (that would require similar analysis). Any help would be greatly appreciated.

Regards,

Kieran

[#2]

* minor correction - that should have read "....no gaps along the X axis"

[#3 · Accepted solution]

Only thing that comes to my mind is stacking the data and then deleting the rows with missing values. Or if the data doesn't need to have specific values on specific rows, moving values in such a way that missing values are in end and then scaling control chart to hide the missing values.

Edit:

Seems like you can create Ordinal (or nominal) column for ordering. Then set values on same row as missing:

No idea how this will affect calculations or if you will have to enable / disable this from Control Chart builder:

[#4]

Many thanks for taking the time to help me out with this Jarmo!

[#5 · Accepted solution]

FWIW, you could get Control Chart Builder to skip values in the way shown in the attached table. Probably the three formulas shown could be combined into one. And there's probably a smarter formula which I didn't see.

[#6]

One way to create B Sample Number column with single formula is:

If(!Is Missing(:B Result),
	Col Cumulative Sum(1, Is Missing(:B Result)),
	.
)

If current :B Result is missing set it as missing, else we use Col Cumulative Sum of 1 and group it by missing values, which allows us to skip them in cumulative sum calculation.

[#7]

Nice!

[#8]

Thanks Ian. I appreciate you taking the time to help me out with this!