cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
mjoner
Level VI

Color Cells based on formula result

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

 

17 REPLIES 17
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);
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.

vince_faller
Super User (Alumni)

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
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.

vince_faller
Super User (Alumni)

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
ms
Super User (Alumni) ms
Super User (Alumni)

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

ms
Super User (Alumni) ms
Super User (Alumni)

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