cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
markschahl
Level V

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

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 SnippetData Table Snippet

 

1 ACCEPTED SOLUTION

Accepted Solutions
jerry_cooper
Staff (Retired)

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

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

View solution in original post

3 REPLIES 3
dale_lehman
Level VII

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

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.

jerry_cooper
Staff (Retired)

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

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 )
)
markschahl
Level V

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

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