topic Re: Finding first and last elements in a column with a By variable in Discussions

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

markschahl — Mon, 30 Apr 2018 23:33:16 GMT

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

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

dale_lehman — Tue, 01 May 2018 15:52:35 GMT

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.

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

jerry_cooper — Wed, 02 May 2018 15:05:53 GMT

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

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

markschahl — Fri, 20 Jul 2018 13:25:02 GMT

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...