topic Cycle Detected in calculation in Discussions

Cycle Detected in calculation

ealindahl — Sat, 10 Jun 2023 23:20:35 GMT

I know JMP is not a spreadsheet and I might need to calculate this differently than I would in excel, but I cannot figure out the workaround.

I have a calculation where a variable is relying on itself and that gives me a "cycle detected for data column" error

I have a tank that has solution being added in over time. I know how much I have added and the tank's total volume. I also take volume out of the tank at sample times. I need to calculate how much of solution A is being removed with each sample volume. But I can't figure out how to do that without getting this cycle error. Please help!

	New Column( "Soln A Removed in Current Draw (mL)",
		Numeric,
		"Continuous",
		Formula(
			:Name( "Soln A concentration before draw (mL/L)" ) * (
			:Name( "Manually Recorded Sample Volume (mL)" ) / 1000)
		),
	);
	New Column( "Soln A concentration before draw (mL/L)",
		Numeric,
		"Continuous",
		Formula(
			If(
				(:Name( "Soln A added (mL)" ) 
				/
				:Name( "Culture Volume Pre-Drawdown (mL)" )
				) 
				* 1000,
				(
					(
						:Name( "Soln A added (mL)" )
						-
						Lag(
							:Name( "Cumulative Soln A Removed (mL)" ), 1 
						)	
					) 				
				/
				:Name( "Culture Volume Pre-Drawdown (mL)" )
					
				) * 1000
			)
		),
	);
	New Column( "Cumulative Soln A Removed (mL)",
		Numeric,
		"Continuous",
		Formula(
			Col Cumulative Sum(
				:Name( "Soln A Removed in Current Draw (mL)" ),
				:Batch Number
			)
		),
	);
	New Column( "Soln A added per current Tank Volume (mL/L)",
		Numeric,
		"Continuous",
		Formula(
			(:Name( "Soln A added (mL)" ) * 1000) /
			:Name( "Culture Volume Pre-Drawdown (mL)" )
		),
	);

Re: Cycle Detected in calculation

Mark_Bailey — Fri, 16 Oct 2020 16:58:18 GMT

You are correct. JMP is not a spreadsheet.

You might think of a JMP data table as a collection of observations (i.e., data set). As such, each change in volume is a new row. So you might have one column to represent the volume added or removed and another to represent the current volume.

I think the first column, Solution A Removed in Current Draw, should be a value that is entered, not computed. That might break the cyclical references. Possibly the same for the last column, Solution A Added in Current Draw. Adjust the formulas accordingly.

Re: Cycle Detected in calculation

ealindahl — Fri, 16 Oct 2020 17:10:26 GMT

Yea it would be great if I could enter the amount removed, but I would only know the amount removed based on the current concentration which, you can see, also relies on the amount removed previously.

Re: Cycle Detected in calculation

txnelson — Fri, 16 Oct 2020 19:00:35 GMT

Here is a different take on the problem. I couldn't completely test it, given the data you provided, but I think you will see the approach I am taking, and you should be able to build upon it.

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Soln A Removed in Current Draw (mL)" );
dt << New Column( "Soln A concentration before draw (mL/L)" );
dt << New Column( "Cumulative Soln A Removed (mL)" );
dt << New Column( "Soln A added per current Tank Volume (mL/L)" );


For Each Row( 

// Soln A added per current Tank Volume (mL/L)
	:Name( "Soln A added per current Tank Volume (mL/L)" )  = (:Name( "Soln A added (mL)" )  * 1000) /
	:Name( "Culture Volume Pre-Drawdown (mL)" ) ;
	
// Soln A concentration before draw (mL/L)	
	If( :Name( "Soln A added (mL)" )  / :Name( "Culture Volume Pre-Drawdown (mL)" )  * 1000,
		:Name( "Soln A concentration before draw (mL/L)" )  = :Name( "Soln A added (mL)" ) 
		-Lag( :Name( "Cumulative Soln A Removed (mL)" )  ) / :Name( "Culture Volume Pre-Drawdown (mL)" )  *
		1000
	);

// Soln A Removed in Current Draw (mL)
	:Name( "Soln A Removed in Current Draw (mL)" )  = :Name( "Soln A concentration before draw (mL/L)" )  *
	(:Name( "Manually Recorded Sample Volume (mL)" )  / 1000);

// Cumulative Soln A Removed (mL)
	curBatch = :Batch Number ;
	curRow = Row();
	:Name( "Cumulative Soln A Removed (mL)" )  = Sum(
		:Name( "Soln A Removed in Current Draw (mL)" )[dt << get rows where(
			:Batch Number == curBatch & Row() <= curRow
		)]
	);
);

Re: Cycle Detected in calculation

ealindahl — Mon, 19 Oct 2020 19:16:00 GMT

I tried your method on a slightly different table (i only added the bach number column, but I think the issue is still there. Instead of an error message I get blank cells. Is there any workaround to allow a variable to rely on its last value?

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Soln A Removed in Current Draw (mL)" );
dt << New Column( "Soln A concentration before draw (mL/L)" );
dt << New Column( "Cumulative Soln A Removed (mL)" );
dt << New Column( "Soln A added per current Tank Volume (mL/L)" );


For Each Row( 

// Soln A added per current Tank Volume (mL/L)
	:Name( "Soln A added per current Tank Volume (mL/L)" )  = (:Name( "Soln A added (mL)" )  * 1000) /
	:Name( "Culture Volume Pre-Drawdown (mL)" ) ;
	
// Soln A concentration before draw (mL/L)	
	If( :Name( "Soln A added (mL)" )  / :Name( "Culture Volume Pre-Drawdown (mL)" )  * 1000,
		:Name( "Soln A concentration before draw (mL/L)" )  = :Name( "Soln A added (mL)" ) 
		-Lag( :Name( "Cumulative Soln A Removed (mL)" )  ) / :Name( "Culture Volume Pre-Drawdown (mL)" )  *
		1000
	);

// Soln A Removed in Current Draw (mL)
	:Name( "Soln A Removed in Current Draw (mL)" )  = :Name( "Soln A concentration before draw (mL/L)" )  *
	(:Name( "Manually Recorded Sample Volume (mL)" )  / 1000);

// Cumulative Soln A Removed (mL)
	curBatch = :Batch Number ;
	curRow = Row();
	:Name( "Cumulative Soln A Removed (mL)" )  = Sum(
		:Name( "Soln A Removed in Current Draw (mL)" )[dt << get rows where(
			:Batch Number == curBatch & Row() <= curRow
		)]
	);
);

Re: Cycle Detected in calculation

txnelson — Mon, 19 Oct 2020 19:59:00 GMT

The issue appears to be that the column value for "Soln A concentration before draw (mL/L)" was always getting set to a missing value. In JMP adding together two values where one of them is a missing value always results in a missing value.....which can be over ridden by using the Sum() function. Here are the results I get after the change. The changed JSL is below

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Soln A Removed in Current Draw (mL)" );
dt << New Column( "Soln A concentration before draw (mL/L)" );
dt << New Column( "Cumulative Soln A Removed (mL)" );
dt << New Column( "Soln A added per current Tank Volume (mL/L)" );


For Each Row( 

// Soln A added per current Tank Volume (mL/L)
	:Name( "Soln A added per current Tank Volume (mL/L)" ) = (:Name( "Soln A added (mL)" ) * 1000) /
	:Name( "Culture Volume Pre-Drawdown (mL)" );
	
// Soln A concentration before draw (mL/L)	
	If( :Name( "Soln A added (mL)" ) / :Name( "Culture Volume Pre-Drawdown (mL)" ) * 1000,
		:Name( "Soln A concentration before draw (mL/L)" ) = Sum(
			:Name( "Soln A added (mL)" ),
			-1 * Lag( :Name( "Cumulative Soln A Removed (mL)" ) )
		) / :Name( "Culture Volume Pre-Drawdown (mL)" ) * 1000
	);

// Soln A Removed in Current Draw (mL)
	:Name( "Soln A Removed in Current Draw (mL)" ) = :Name( "Soln A concentration before draw (mL/L)" ) * (
	:Name( "Manually Recorded Sample Volume (mL)" ) / 1000);

// Cumulative Soln A Removed (mL)
	curBatch = :Batch Number;
	curRow = Row();
	:Name( "Cumulative Soln A Removed (mL)" ) = Sum(
		:Name( "Soln A Removed in Current Draw (mL)" )[dt << get rows where( :Batch Number == curBatch & Row() <= curRow )]
	);
);