Number of rows until event

[#1]

Without using JSL, is there a way to calculate a column that tells you how many rows are left until an event happens?

The following formula works but needs the user to click n times in "Apply"

Can we put this formula inside a for loop that 'applies it' n times?

[#2]

You could use more complicated formula. Something like this could work (might not handle all special cases correctly):

If(Row() == 1,
	m_events = Loc(:Event << get as matrix, 1);
);
smallerRows = Loc(Row() <= m_events);
If(N Items(smallerRows) == 0,
	0,
	rowNextEvent = m_events[smallerRows[1]];
	If(rowNextEvent,
		rowNextEvent - Row();
	);	
);

[#3]

Maybe we can use is the fact that the solution, if the row order is reversed, is self populated in JMP?

For example, this will reverse the order of the events.

Event reversed:

:Event[(N Rows() - Row()) + 1]

Now the values self-populate as the calculation works from top to bottom

n rows after the reversed event:

If( :Event inverted == 1, 0,
Lag( :n rows after inverted event, 1 ) + 1
)

Finally, we reversed that column.

:n rows after reversed event[(N Rows() - Row()) + 1]

Any ideas on how to do this in one formula which is easy to interpret?

[#4]

At this point I will have to ask: what are we looking for here? Single formula which is also self-explanatory?

The formula I did provide is a single formula (will require small change to handle cases if there are no events after some specific point) but hardly self-explanatory, but can made closer to one by using supporting variables with good names.

[#5]

I am after a formula that is very simple to explain to new JMP users.

• Adding my original formula inside a loop, so you don't need to click in apply, will be the easiest to explain.
• Reversing the order is intuitive but takes extra steps (see above)
• A solution that creates temporary variables looks like a script made into a formula (programmer mindset, not ideal for new users).

So maybe it is as simple as asking, how can I loop a formula so it is applied N times?

[#6]

Nothing simple which wouldn't require JSL doesn't come to my mind which would allow you to use the original formula without pressing apply or using rerun formulas multiple times. Even building formulas is a bit difficult due to the no variables in this case (even for loop has variable for index).

For example this cannot easily handle last rows if there is no events after them (also the formula isn't simple to explain):

If(:Event == 1,
	0,
	For(i = 1, i <= N Rows() - Row(), i++,
		If(Lag(:Event, -1*i) == 1,
			Break();
		);
	);
	i;
);