Formula for change over time

DELANDJ1985 — Mon, 04 Nov 2024 17:37:46 GMT

Looking for a formula to access change over time at a row level moving from left to right i want to assess the percent shift from the 1st reading to the next and then assess all following reading back against the first and the previous reading to access shift overtime. If any are downward shifts greater than or equal to 20% indicate a 1 in a result column.

Re: Formula for change over time

txnelson — Tue, 05 Nov 2024 04:01:54 GMT

Here is a formula that finds the first percent shift, and then goes through all of the remaining columns and finds and values and does a percent change from the initial value, and if it is a negative change > 20% it sets the result to a 1.

I am pretty sure it isn't exactly what you are looking for. I don't understand what " and then assess all following reading back against the first and the previous reading to access shift overtime" precisely means. Study my code and see what it is doing, and then see if you can change it to do exactly what your really want. If not, respond back with some clarification or questions about my JSL.

As Constant( dt = Current Data Table() );
theMatrix = dt[Row(), Index( 2, 23 )];
start = Loc( theMatrix )[1] + 1;
startVal = dt[Row(), start];
firstChange = .;
currentChange = .;
firstValue = .;
For( i = start, i <= 22, i++,
	If(
		Is Missing( dt[Row(), i] ) == 0 & Is Missing( firstValue ) == 1, firstValue = dt[Row(), i],
		Is Missing( dt[Row(), i] ) == 0 & Is Missing( firstChange ) == 1,
			firstChange = (dt[Row(), i] - firstValue) / firstValue,
		Is Missing( dt[Row(), i] ) == 0 & Is Missing( firstChange ) == 0,
			currentChange = (dt[Row(), i] - firstValue) / firstValue;
			If( currentChange < -0.2,
				currentChange = 1;
				break(),
				currentChange = .
			);
	)
);
currentChange;

topic Re: Formula for change over time in Discussions

Formula for change over time

Re: Formula for change over time