Finding first and last elements in a column with a By variable

[#1]

Hi!

I'm investigating what happened to continous variable (tank level in this case) during certain events. I have an Event.Date column that acts as a By variable. Event duration varies, see DayOfEvent column. I want to calculate: LEVEL @ end of event - LEVEL @ beginning of event, i.e. last element - first element by Event.Date. If positive, tank was filled, if negative tank was emptied, if ~0 tank was not used.

Using Col Maximum(LEVEL, Event.Date) - Col Minimum(LEVEL, Event.Date) will give the range, but NOT if negative or positive.

Please refer to the attached graphic of data table snippet.

Thanks!
Data Table Snippet

[#2]

How about using col maximum of event date, by day of event and then creating a column that uses the level if those dates match.  Do the same for the minimums.

[#3 · Accepted solution]

Do either of these work for you?

The first option gives what you asked for (level at end - level at beginning):

If( Lag( :DayOfEvent, -1 ) == 0,
	:LEVEL - Lag( :LEVEL, :DayOfEvent )
)

The second gives that plus what happened in between (current level - level at beginning):

If( :DayOfEvent == 0,
	.,
	:LEVEL - Lag( :LEVEL, :DayOfEvent )
)

[#4]

Jerry:

Thanks! That worked!

Applogies for the delay. Almost three months of travel, including training 100+ engineers at five sites globally how to use JMP, delayed my response. It's been just a bit busy...