topic Re: Dealing with cycled or recurrent formulas in a table (a.k.a. circular references). in Discussions

Dealing with cycled or recurrent formulas in a table (a.k.a. circular references).

FN — Sat, 10 Jun 2023 23:35:04 GMT

I have a formula that is recurrent. JMP can solve it, but it warns about cycles every time I create a new formula.

How can I:

Force a new iteration?
Configure the number of iterations (or stopping criteria) to solve the recurrence?
Stop the evaluation from happening again? Suppress Eval option in the formula menu, did not work.

The function is a bit more complex but not different from the following, which works without complaint.

X (k+1) = 0.99 * X(k)

If(
	Selected() == 1, 1,
	Row() == 1, 1,
	Lag( :X ) * 0.99
)

Re: Dealing with cycled (or recurrent) formulas in a table

FN — Tue, 17 Aug 2021 19:22:09 GMT

For reference, Excel offers the following options to deal with circular references.

https://support.microsoft.com/en-us/office/change-formula-recalculation-iteration-or-precision-in-excel-73fc7dac-91cf-4d36-86e8-67124f6bcce4#:~:text=Iteration%20is%20the%20repeated%20recalculation,is%20called%20a%20circular%20reference.

Re: Dealing with cycled (or recurrent) formulas in a table

Craige_Hales — Wed, 18 Aug 2021 00:51:24 GMT

JMP's formula evaluator works by first evaluating formula columns that only depend on non-formula columns, then by evaluating formula columns that depend on formula columns that are already evaluated. If column A's formula depends on column B, and column B's formula depends on column A, JMP can't choose which one to evaluate first. If you manage to trick JMP into creating a circular dependency, you can't be sure which column was using old data from the other column.

JMP evaluates a column from top to bottom. Within a formula you can use the lag function (or sub-scripting) to look at earlier rows and get the value previously calculated. I'm not sure what happens if you look at future rows, but that value has not been calculated yet.

There are formulas you might think could work based on excel rules, but and plays by the rules above. If you need to make column A refer to a lag of column B and column B refer to a lag of column A, you should make A and B be non-formula columns and write JSL to fill them in. Add that JSL to a table script with a name like "Run Me When The Data Needs To Be Updated".

If you have a recursive function, you can make a JSL function that calls itself. JSL has a fairly low limit on the allowed depth of recursion, 100 or so. You have to write your own if statement that decides when to stop the recursion. Typically a depth parameter is used to stop the recursion, something like (untested, and pointless...)

f = function({depth}, if(depth < 10, /* do something */ f(depth + 1) ) );
f(0)

Re: Dealing with cycled or recurrent formulas in a table (a.k.a. circular references).

ih — Thu, 19 Aug 2021 17:17:46 GMT

The function you provided should work as is, without any warnings. If you are in fact referencing column A in the formula for B and column B in the formula for A, then you might try writing that logic as a single formula or follow @Craige_Hales's advice and do that with a script instead.

Table with your example formula, this should evaluate without warning or error.

New Table( "Untitled 16",
	Add Rows( 10 ),
	New Column( "X",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( If( Selected() == 1, 1, Row() == 1, 1, Lag( :X ) * 0.99 ) ),
		Set Selected
	),
	Set Row States( [0, 0, 0, 0, 1, 0, 0, 0, 1, 0] )
)