Cycle detected for data column

[#1]

Hello, All

I am making a model that requires some "loop feedback", and it is giving me this error:

Do you know how to get around it?

I do not have this problem in Excel using the same formulae

Thank you,

-Jose

UPDATE:

I was able to make it run, but it will only execute the formula on one row at the time.

I am attaching the table script for reference.

You will see that two columns are dependent from each other.

n*Zr*S(ti) = soil saturation at the beginning of the day

n*Zr*S(ti-) = soil saturation at the end of the day

Then for n*Zr*S(ti), I need the value of n*Zr*S(ti-) for the previous day [each day is a row].

The problem I have now is: every time I hit "apply" in the formula editor for n*Zr*S(ti-) [or n*Zr*S(ti)], I get the Cycle detected error, then I press Ignore all cycles, and the formula runs for all columns, until the n*Zr*S(ti) column. If I repeat this process, JMP calculates n*Zr*S(ti), and n*Zr*S(ti-), is calculated automatically [because n*Zr*S(ti)] is needed for next day's n*Zr*S(ti-).

Is there a way to tell JMP "if this cell has not been calculated, use this other value in the meantime", then "if that other cell has a value, update this other one"?

Any help will be appreciated.

-Jose L

[#2]

If you are willing to use a different formula, here is a formula for calculating the simple Mode for a column called :Age

If(Row() == 1, Summarize(count = Count(:age), bygroup = by(:age))); Num(bygroup[Loc(As List(count), Max(count))])

[#3]

Hi, Jim

I tried this method, but it did not work.

I learned about the Mode, though ^_^

-JL

[#4]

JL,

Here is the script that opens the "Big Class" sample data table, which I used in my above example to create the new column and it's formula for the determination of the Mode.  I hope this resolves any confusion as to how to make what I provided work for you.

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA\Big Class.jmp" );

dt << New Column(

       "Mode",

       formula(

              If( Row() == 1,

                     Summarize( count = Count( :age ), bygroup = by( :age ) )

              );

              Num( bygroup[Loc( As List( count ), Max( count ) )] );

       )

  )

;

[#5]

When a table contains more than one formula column, JMP looks at each column to see which other columns need to be evaluated ahead of time.  The dialog means JMP could not figure out which column needed to be evaluated first.

I hope txnelson answer helps.  If not, perhaps you can remove one of the column's dependency on the other column.  If that doesn't work, you can move the logic into a JSL loop, something like

Example 1

ForEachRow(

     column1 = <column1 formula>

     column2 = <column2 formula>

)

or

Example 2

ForEachRow(

     column1 = <column1 formula>

)

ForEachRow(

    column2 = <column2 formula>

)

The first example assumes column2 can be evaluated without future rows of column1 being computed already.

The second example represents the ordering JMP does, figuring out which column should be evaluated first.  All of column1 rows will be available for column2 calculations.