Color Cells based on formula result

[#1]

Hi everyone,

I have a column (lets call it Indicator) with a formula in it that returns a 0 or a 1.

Right now I know how to do this:

formulaResult = :Indicator << Get Values;
:Indicator << Color Cells( "red", Loc(formulaResult==1) );

But what I really want to do is have the data table dynamically respond: if I change the data so that the Indicator switches to 0 (false), I don't want the cell to highlight any more, and if I add more data to the table and the formula calculates, I want the 1's to highlight.

I can't figure out how to do this with Color Cells. I thought that perhaps a strange implementation of the Color Gradient column property might work but after playing around with that for a while I couldn't get the result I wanted. Looking for some ideas.

Michael

[#2]

You can do the calculations with colmin, colmax and colmean:

frr_min = col min(:FRR);
frr_max = col max(:FRR);
frr_avg = col mean(:FRR);

// Color FRR with a gradient
column(hlt_dt, "FRR") << Set Property( "Color Gradient", {"Light Spectral", Range(expr(frr_min), expr(frr_max), expr(frr_avg))});
column(hlt_dt, "FRR") << color cell by value(1);

[#3]

Sorry. This solution doesn't allow me to color values out of a range of interest with one color (say, red), and within the range of interest with a different color (say, white). This col min, col mean, col max solution seems to be a way to simply put some defaults in to do a gradient. What was attractive about @vince_faller's solution is that it creates a custom Gradient that forces exactly two colors (I think) onto the column. What's not attractive to me yet is that I don't understand how to precisely control when I switch from my "good" color to my "bad" color.

[#4 · Accepted solution]

So basically I'm just faking a color gradient that gradients over a tiny window.  So for only two colors you only need 4 points. 0, some number very close to 0, some number very close to 1, and 1.  If you wanted more than 2 colors you'd have to calculate values from 0 to 1 at each point in your range you wanted.  for instance if you wanted 110, it you'd have to add two values at 10/35 and 10/35+.0000001

Names Default to Here(1);
dt = Open("$SAMPLE_DATA\Big Class.jmp");
Column(dt, "weight") << Set Property(
	"Color Gradient" ,
		{{"Something", 16387, 
		{{255, 0, 0}, {0, 255, 0}, {0, 255,	0}, {255, 0, 0}}, //list of colors
		{0, .00000001, .9999999, 1}}, //corresponding locations from 0 to 1 
		Range( {100, 135, 117.5} ) //minimum, maximum and center.  0, 1, and .5 is another way to look at it
			
		}
);
Column(dt, "weight") << Color Cell By Value(1);

[#5]

This is clumsy, and there should be a more direct/obvious way to do it in JMP, but this solution can work. Thank you.

[#6]

Absolutely agree.  You should definitely put in a feature request for that at support@jmp.com. 

[#7]

Maybe not obvious, but you may find this approach less "clumsy":

Names Default To Here(1);
dt = Open("$SAMPLE_DATA\Big Class.jmp");
dt << New Column("highlight cells",
    Formula(If(100 < :weight < 135, :weight << color cells("Red", Row()); ., .)),
    Hide(1)
);

[#8]

This approach works for me unless you change the weights. I thought I could simply add a request to set the else parameter in the If() statement to Color Cells("White",Row()) but that doesn't seem to work.

Also it requires a new column for each parameter that has spec limits, although I suppose we could write a creative formula that iterates through all of the columns, checks for a Spec Limits column property, and if it finds one goes and colors the appropriate columns? At least the extra column(s) are hidden.

[#9 · Accepted solution]

Here's the same approach with a white-coloring Else clause. The hidden column is here numeric by default so both the Then and the Else must return something numeric, e.g. a missing value.

dt = Open("$SAMPLE_DATA\Big Class.jmp");
dt << New Column("highlight cells",
    Formula(If(100 < :weight < 135, :weight << color cells("Red", Row()); ., 
    :weight << color cells("White", Row()); .)),
    Hide(1)
);