Col Median (actually: Substitute) - what's wrong?

[#1]

I don't understand what's wrong with this file.

height_rel and height_rel 2 have exactly the same Formula.

But in height_rel 2 the values are missing.

Before saving the file, the Formula editor complained that there were too few arguments in the Col Median funktion.

One could convince the Formula Editor that everything is OK by double-clicking on the formula editor and then closing all windows by clicking on OK, Ok, Ok.

After saving the file, this is not possible. I did not find a way to revive the formula.

If you copy and paste the column, the copy is OK.

[#2 · Accepted solution]

Try going back to formula editor and press Apply. Or check Suppress Eval -> Apply -> uncheck Suppress Eval -> Apply again. You can also try Current Data Table() << Rerun formulas.

[#3]

Many thanks - 3 ways to fix my problem

Just via "OK" it didn't work.

Digging deeper - why did it happen ...

Is there a reason why myFormula3 (with substitution of Head and Aggregation) and myFormula4 (with subsequent substitution of Head and Aggregation) don't work? myFormula1 and myFormula2 (just substituting one of both) do.
(same for Jmp 16.2 and Jmp 17)

name Expr(myFormulax) look quite the same:

Name Expr(myFormula1) = Divide(:height, Col Median(:height));
Name Expr(myFormula2) = :height / Col Median(:height);
Name Expr(myFormula3) = Divide(:height, Col Median(:height));
Name Expr(myFormula4) = Divide(:height, Col Median(:height));

Unfortunately, before saving an re-opening the file none of the above 3 solutions work

Would be nice if a Current Data Table() << Rerun formulas;  could solve the problem.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

myFormula1 = Expr(__myHead__( :height,Col Median( :height ) ));
Substitute Into( myFormula1,
	Expr( __myHead__ ), Expr( Divide ),
					
);

myFormula2 = Expr(Divide( :height, __aggregation__( :height ) ));
Substitute Into( myFormula2,
	Expr( __aggregation__ ),Expr( Col Median )
					
);

myFormula3 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula3,
	Expr( __myHead__ ), Expr( Divide ),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula4 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula4,Expr( __myHead__ ), Expr( Divide ));
Substitute Into( myFormula4,Expr( __aggregation__ ),Expr( Col Median ));


Show( Name Expr( myFormula1 ) );
Show( Name Expr( myFormula2 ) );
Show( Name Expr( myFormula3 ) );
Show( Name Expr( myFormula4 ) );

Eval( Substitute( Expr( New Column( "new1", Formula( __f__ ) ) ), Expr( __f__ ), Name Expr( myFormula1 ) ) );
Eval( Substitute( Expr( New Column( "new2", Formula( __f__ ) ) ), Expr( __f__ ), Name Expr( myFormula2 ) ) );
Eval( Substitute( Expr( New Column( "new3", Formula( __f__ ) ) ), Expr( __f__ ), Name Expr( myFormula3 ) ) );
Eval( Substitute( Expr( New Column( "new4", Formula( __f__ ) ) ), Expr( __f__ ), Name Expr( myFormula4 ) ) );

[#4]

What's wrong with these:

myFormula3 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula3,
	Expr( __myHead__ ), Expr( Divide ),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula4 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula4,Expr( __myHead__ ), Expr( Divide ));
Substitute Into( myFormula4,Expr( __aggregation__ ),Expr( Col Median ));

?

[#5 · Accepted solution]

I have "solved" this type of issue earlier at work, but I cannot remember anymore how and why it happened. Also haven't recorded it to my tips&tricks list, so cannot find the reasoning and solution easily. The second expression seems to be getting stuck if it is inside other expression when built like this.

One workaround:

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

myFormula3 = Expr(__myHead__(:height, __aggregation__(:height)));
Substitute Into(myFormula3, Expr(__myHead__), Expr(Divide), Expr(__aggregation__), Expr(Col Median));

myFormula4 = Expr(__myHead__(:height, __aggregation__(:height)));
Substitute Into(myFormula4, Expr(__myHead__), Expr(Divide));
Substitute Into(myFormula4, Expr(__aggregation__), Expr(Col Median));

Show(Name Expr(myFormula3));
Show(Name Expr(myFormula4));

new_col = Eval(
	Substitute(
		Expr(New Column("new3", Formula(__f__))),
		Expr(__f__), Parse(Char(Name Expr(myFormula3)))
	)
);

[#6]

I feared that I would need some nested If statements and many times the same code again to make it work.

Very cool, "dirty" workaround - thanks

... I wish one didn't need it:

fix the bug in Substitute 
edit: not needed

@julian : Is there a webinar which explains why the above code just works with a Parse(Char))?

[#7 · Accepted solution]

I don't think the issue is with Substitute but rather with something else. Maybe JMP doesn't understand that Col Median is supposed to be function in this case as the replacement is a bit weird (brackets are missing). Here is most likely "correct" way to do this (note () after Divide and Col Median).

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

myFormula3 = Expr(__myHead__(:height, __aggregation__(:height)));
Substitute Into(myFormula3, Expr(__myHead__), Expr(Divide()), Expr(__aggregation__), Expr(Col Median()));
show(Name Expr(myFormula3));

new_col1 = Eval(
	Substitute(
		Expr(New Column("new3", Formula(__f__))),
		Expr(__f__), Name Expr(myFormula3)
	)
);

[#8]

Wow, important finding!
To be honest, I did not know that one has to use the brackets - I wouldn't even have bet that it works.

Thanks a lot, I definitely will add a bookmark for this post to my tips&tricks list

[#9]

Just to add a bit of extra mystery, it appears only one of the substituted functions needs to include the "()" so both of the formulas below will work.

Edit: And upon further digging, it seems that JMP will tolerate only 1 function in an expression passed without the "()" in the substitution. Three more formulas are at the bottom where I wrap another function around your existing expression, and it will work correctly with any two functions substituted with the parentheses. I suspect there's an internal pass JMP does to make inferences about an expression in order to make it legal and it's only going to make one adjustment and then break.

myFormula3 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula3,
	Expr( __myHead__ ), Expr( Divide() ),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula4 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula4,
	Expr( __myHead__ ), Expr( Divide ),
	Expr( __aggregation__ ),Expr( Col Median() )					
);

myFormula5 = Expr(__top__(__myHead__( :height, __aggregation__( :height ) )));
Substitute Into( myFormula5,
	Expr(__top__), Expr(Log),
	Expr( __myHead__ ), Expr( Divide),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula6 = Expr(__top__(__myHead__( :height, __aggregation__( :height ) )));
Substitute Into( myFormula6,
	Expr(__top__), Expr(Log()),
	Expr( __myHead__ ), Expr( Divide()),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula7 = Expr(__top__(__myHead__( :height, __aggregation__( :height ) )));
Substitute Into( myFormula7,
	Expr(__top__), Expr(Log()),
	Expr( __myHead__ ), Expr( Divide),
	Expr( __aggregation__ ),Expr( Col Median() )					
);

[#10]

I don't know of any webinar that covers that (or even discusses this issue), but it's a clever workaround to force JMP to parse that expression again. I'm really impressed with @jthi's most recent solution of including the () in the substitution, I would not have guessed that would work but it seems to help JMP recognize that you're passing in a function and it handles the rest. Surprising and interesting solution to what is a mysterious and confusing issue!