topic Re: color a cell by formula in Discussions

color a cell by formula

CumulativeMean2 — Fri, 11 Oct 2024 04:26:26 GMT

i have a data table in wide format. it has the spec limit. it works fine by doing quality & process -> manage limit -> color out of spec value. The fail value are color using this method. i would like to view it in tall format. See below transposed table.
The data table are transposed then update with limit (column name with LSL and USL). Label is the parameters, 1 to 20 are for different parts.

i would like to color a cell or the value if the reading of the part is < LSL or reading is > USL. can i do it with cols -> formula or does it need JSL?

this is JMP 17.

Re: color a cell by formula

jthi — Fri, 11 Oct 2024 05:06:55 GMT

As you are performing the checks on row level and colors are generally set on column level, I think you will need to utilize JSL. The JSL for that isn't too difficult and if you can provide example data, JMP Community can most likely help you build it.

One way to build the script would be:

Get measurement columns into a list/associative array (continuous columns between Label / LSL or columns with just numbers in the name)
Use For Each Row to loop over the table
Compare values on the row to the limits while updating the object created in step one with the rows which should be colored for each column
After you have finished looping of the rows, which to looping over the object created in step 1 and set the colors using << Color Cells

Re: color a cell by formula

CumulativeMean2 — Fri, 11 Oct 2024 07:54:25 GMT

here is example data.

column "label" is the parameter. each label has unique LSL and USL.

1 to 20 in column header are different parts. every part has a unique value for each parameter.

for example, if row 3, column "1" with reading of "0.37209999561" is out of LSL & USL of (0.070000000298, 1), then the "0.37209999561" font color changed to red.

Re: color a cell by formula

jthi — Fri, 11 Oct 2024 08:33:30 GMT

Not the simplest way of doing this, but might work for you. Note that you might have to add extra checks if you have one-sided specification limits

Names Default To Here(1);

dt = Open("$DOWNLOADS/Transpose of u4_trimmed.jmp");

cols = dt << Get Column Names("String");
labelidx = Contains(cols, "Label");
partcols = Remove From(cols, labelidx + 1, Contains(cols, "LSL") - labelidx - 1);

m = J(N Rows(dt), N Items(partcols), 0);

For Each Row(dt,
	m[Row(), 0] = dt[Row(), partcols] < :LSL | dt[Row(), partcols] > :USL
);

For Each({partcol, idx}, partcols,
	color_rows = Loc(m[0, idx]);
	If(N Items(color_rows) > 0,
		Column(dt, partcol) << Color Cells("Red", color_rows);
	);
);

Re: color a cell by formula

CumulativeMean2 — Wed, 23 Oct 2024 06:42:34 GMT

it works. sorry for late response. i was trying to figure it out the script, but it seems like this is going to take more time.

Re: color a cell by formula

SophieGray — Fri, 08 Nov 2024 13:40:02 GMT

Thank you so much for the solution.