topic Re: Number of rows until event in Discussions

Number of rows until event

FN — Sat, 10 Jun 2023 23:37:17 GMT

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?

Re: Number of rows until event

jthi — Tue, 21 Sep 2021 20:37:06 GMT

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();
	);	
);

Re: Number of rows until event

FN — Wed, 22 Sep 2021 12:11:18 GMT

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?

Re: Number of rows until event

jthi — Wed, 22 Sep 2021 14:35:53 GMT

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.

Re: Number of rows until event

FN — Wed, 22 Sep 2021 15:09:21 GMT

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?

Re: Number of rows until event

jthi — Wed, 22 Sep 2021 16:47:39 GMT

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