56163_en-US

liliconcom

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

 

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

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

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.