World Statistics Day was yesterday, but we’re celebrating all week long! This celebration means acknowledging the impact statistics has on our world. Who is your favorite statistician? Share with us who they are and why they top your favorites list.
Choose Language Hide Translation Bar
Highlighted
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)" )
		),
	);
5 REPLIES 5
Highlighted

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.

Learn it once, use it forever!
Highlighted
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. 

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