nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Color Cells based on formula result

mjoner

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

Accepted answers

vince_faller

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

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

All comments

Try the "Value Colors" property with "Color Cells by Value" checked.

Example:

// Example table
dt = New Table("test",
    Add Rows(10),
    New Column("data", Formula(Random Uniform())),
    New Column("Indicator",
        Set Property("Value Colors", {. = -12632256}),
        Formula(:data < 0.5),
        Color Cell by Value
    )
);

// Test if dynamic
Wait(1);
:data << eval formula;
Wait(1);
dt << add rows(10);

mjoner

Awesome! I think I oversimplified my problem a bit. Let's try this generalization.

Now my Indicator is now a count of defects (0, 1, 2, 3, 4, etc.). How can I color everything >=1 with the same color, and not color the zeroes?

Mark_Bailey

You just need to change the logic used in the column formula, for example, :data > 0.

mjoner

Mark,

Of course. It makes sense that I could just create another indicator column. The only problem is this will color the cells in the indicator column rather than highlighting the number of defects, like this:

What I'd prefer is this:

Any ideas?