- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
How to evaluate a column bottom to top?
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
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to evaluate a column bottom to top?
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to evaluate a column bottom to top?
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()];