Choose Language Hide Translation Bar
Highlighted
pmroz
Super User

Re: Color Cells based on formula result

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);
Highlighted
mjoner
Level VI

Re: Color Cells based on formula result

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.

Highlighted
vince_faller
Super User

Re: Color Cells based on formula result

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);

 

Vince Faller - Predictum

View solution in original post

Highlighted
mjoner
Level VI

Re: Color Cells based on formula result

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

Highlighted
vince_faller
Super User

Re: Color Cells based on formula result

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

Vince Faller - Predictum
Highlighted
ms
Super User ms
Super User

Re: Color Cells based on formula result

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)
);
Highlighted
mjoner
Level VI

Re: Color Cells based on formula result

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.

Highlighted
ms
Super User ms
Super User

Re: Color Cells based on formula result

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)
);

 

View solution in original post

Article Labels

    There are no labels assigned to this post.