Minimum function

Question

I have two different dates for different people. First column (A) is person name. Second column is (B=index event date) and the third one is (C=intervention date). The fourth column is  the difference (D) between the two dates "B-C/ in days(1)" .
My question is how to let JMP choose specific value in D for each person (A). I need to select the value in the following timeframes:
1- The closest value in D before the index event date (B). Let's say one person has the following date difference values.  0, -1, -10. I want zero in this case

2- The first value in D within 2 weeks after A (0 day up to 14 days) let's say a person has 2, 8, 14. I want 2
3- The first value in D after 2 weeks from A (15 days or more) Let's say a person has 17, 25, 35. I want 17

brady_brady · Answer

Hi, As Jed mentions, more context is better, but I think I see what you want. In case (1), a maximum, but only among those differences less than or equal to zero. In case (2), a minimum, but only among those differences between 0 and 14, inclusive. In case (3), a minimum, but only among those differences 15 or greater. If this is what you want, then in each case you can use the same idea: 1) Dynamically set the data you DON'T want to missing, and then 2) Compute the max or min of the data, while ignoring the missing data. In the code below, each formula has the same structure: Col <> ( If ( I want to ignore the data, make it missing, else use the original data), <> ). So which data do we want to ignore? In case ( 1 ), it is :diff > 0... any difference greater than 0 In case ( 2 ), it is ! ( 0 <= :diff <= 14)... any difference not between 0 and 14 inclusive In case ( 3 ), it is :diff < 15... any difference less than 15 Running the code you will see a table like the one below. In my code, the grouping column is the "person" column... and I've just simulated the differences, as from your description that's the column of interest. Cheers, Brady Names Default To Here( 1 ); nr = 50; dt = New Table( "ex", New Column( "Person", character, < 0, ., :diff ), :Person ) ) ); dt << New Column( "form_2", formula( Col Min( If( !(0 <= :diff <= 14), ., :diff ), :Person ) ) ); dt << New Column( "form_3", formula( Col Min( If( :diff < 15, ., :diff ), :Person ) ) );

Jed_Campbell · Answer

Any chance you could share a sample data table to help make this more clear? The function Tables...Anonymize can make things easier to share, if PII or proprietary info can't be shared.