Cycles Detected is a major problem for my work.

[#1]

Hello JMP community 

"Cycles detected" is a major problem (that occurs frequently) due to simulating business rules which are NOT cyclic row-wise. However, JMP is unable to handle a very simple row-wise dependency that excel can. Quite frankly, I do not like hearing that JMP is not spreadsheet because I think a significant portion of users probably came up w/ this sort of problem. I am fighting this problem constantly as data is classified per records (ie. date/time) and as such is subject prior records. Why would JMP provide lag function if they did not want to make it easier handling row-wise operations (and it is great, I constantly use it). But doing so opens the cycles detected problem, and again spreadsheet thinking-wise, there are no conflicts. Excel can handle it easily, JMP cannot. I hold a Pro license and a feedback like this back to my upper management frankly would not look great. To me the major advantage of JMP Pro is the user friendly ability of complex modeling, data science and machine learning on big data. Current described problem is an issue against the user friendliness of the tool. If I can't do anything else, I need to export from JMP so I can do this w/ Excel (which is hassle) 

My intentions from this post : 

1- Is there anything I can do to bring this to developers attentions ? 

E.g. I tried "Ignore Errors" and "Ignore all Cycles" independently - I had an expectation that these may have bypassed the necessary checks and could perhaps let the formulas run through (they did not)

2- Is there any formula based approach (even if script, this needs to be in a formula) that can be used ? I am attaching 2 samples below - same calculations, one in JMP and the other in xls. 

A is a free input col. 

B initializes to A for record = 1 , and then  is either A or prior value of B ( ie. itself using lag function) dependent on C

C is A - prior value of B

thanks.

[#2]

Hi @altug_bayram,

This sounds like such a frustrating problem to encounter all the time in your work! I agree that it seems like "Ignore all cycles" should do as it says, and let the formulas proceed because you are asserting to JMP that no dependency is actually there. This sounds like a defect, and the best way to get this to the developers is to email support@jmp.com explaining the issue as you have here. 

As a workaround, the fix is to not create even the appearance of a dependency by copying the entire formula for column C, and replacing references to C with that formula in Column B:

If( :Record == 1,
	:A,
	If( If( :Record > 1, :A - Lag( :B, 1 ), . ) < 1.5,
		:A,
		Lag( :B, 1 )
	)
)

You can keep your formula for C as it is, and your table will populate just fine. This doesn't raise the alarms for a cycle because, in a single formula, it is clear to JMP that references to B in the column formula for B are happening through Lag(). It seems when the formulas are in different columns, the cycle detection fails to take the Lag() into account and just sees B referencing C referencing B.    

I hope this is helpful for your work until a fix of "Ignore All Cycles" can happen!

@julian 

[#3 · Accepted solution]

This script produces the same answer as the Excel table and looks a lot like your column formulas.

dt = Open( "/Z:/cyclic_error_sample.jmp" );;
dt:b<<deleteformula();
dt:c<<deleteformula();
For Each Row( dt,
    dt:c = If( dt:record > 1,
        dt:a - lag(dt:b);//
    ,//
        .
    );
    dt:b = If( dt:record == 1,
        dt:a;//
    , //
        If( dt:c < 1.5,
            dt:a;//
        , //
            lag(dt:b)
        )
    );	
);

The script puts the evaluation of C before the evaluation of B, row-by-row, unlike JMP column formulas which evaluate column-by-column. It took me a minute of staring at your formulas to see that C needs to evaluate first, because it is only dependent on B's previous row. Then B can evaluate because it is dependent on the C value we just computed for the current row.

Studying it a bit more...just copy the C formula into column B in place of dt:C, like this:

Removes the reference to column C

Which will allow you to delete column C if you don't really need it. It also means the columns will update automatically, unlike the first script which you'd need to rerun.

[#4]

My actual parameters were more complicated than shown here in this simple example. Julian's solution is equally acceptable but I figure trying to put all complexity into a single formula would be too much, hence  I went w/ the script from Craige (that worked). 

I will try sending a note to JMP developers so that (1) they can perhaps clarify the points of "Ignore errrors" and "Ignore all cycles" - which again to me - sound like you can bypass a cycles detected problem , but they clearly did not. (2) and perhaps they can consider putting an option to JMP to bypass if a real cyclic does not exist.