cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ealindahl
Level II

Cycle Detected in calculation

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)" )
		),
	);
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Cycle Detected in calculation

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

soln.PNG

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 )]
	);
);
Jim

View solution in original post

5 REPLIES 5

Re: Cycle Detected in calculation

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.

ealindahl
Level II

Re: Cycle Detected in calculation

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. 

txnelson
Super User

Re: Cycle Detected in calculation

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
		)]
	);
);

 

Jim
ealindahl
Level II

Re: Cycle Detected in calculation

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
		)]
	);
);
txnelson
Super User

Re: Cycle Detected in calculation

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

soln.PNG

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 )]
	);
);
Jim