topic Re: How to evaluate a column bottom to top? in Discussions

How to evaluate a column bottom to top?

zathe — Thu, 11 Apr 2024 13:34:58 GMT

I need to count negative indexes from a particular cell, how you make a single column evaluate bottom to top?

Col 29 Col 31

0 -5

0 -4

0 -3

0 -2

0 -1

1 -42

1 -41

1 -40

If( Lag( :Column 29, -1 ) == 1 & :Column 29 == 0,
-1,
Lag( :Column 31, -1 ) - 1
)

Re: How to evaluate a column bottom to top?

jthi — Thu, 11 Apr 2024 15:12:56 GMT

Based on your example data, you might be able to use group of your first column and then use count of items in that group and cumulative sum

Col Cumulative Sum(1, :Column 1) - 1 - Col Number(:Column 1, :Column 1)

Re: How to evaluate a column bottom to top?

txnelson — Thu, 11 Apr 2024 15:33:39 GMT

Jarmo has a strong possible way of solving the issue. I have taken a longer route, which demonstrates one way of working backwards. The approach does all of the work just as the formula starts processing by using an As Constant() function, which runs the contained code once. In this section, it loops from the last row to the first row and creates a result matrix called "Values". Then in the actual formula, the only thing to do is to apply the value from the Values matrix to each of the rows.

As Constant(
	values = J( 1, N Rows( Current Data Table() ), . );
	For( i = Length( values ) - 1, i >= 1, i--,
		if(:col 29[i] == 0 & :Col 29[i + 1] == 1,
		values[i]=1,
		:col 29[i] == 0 & :Col 29[i + 1] == 0,
		values[i] = values[i-1]-1,
		values[i]=.
	));
	Show( values );
);
values[Row()];