topic Re: Minimum function in Discussions

Minimum function

AliSheffeh — Thu, 08 Jun 2023 16:32:30 GMT

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

Re: Minimum function

Jed_Campbell — Wed, 01 Mar 2023 19:43:34 GMT

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.

Re: Minimum function

brady_brady — Wed, 01 Mar 2023 23:51:06 GMT

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 <<max or min>> ( If ( I want to ignore the data, make it missing, else use the original data), <<grouping variable>> ).

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, <<set values( Words( "abcde", "" )[J( nr, 1, Random Integer( 1, 5 ) )] ) ),
	New Column( "diff", <<set values( J( nr, 1, Random Integer( -10, 30 ) ) ) )
);
dt << sort( by( :person, :diff ), order( ascending, ascending ), replace table );
dt << New Column( "form_1", formula( Col Max( If( :diff > 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 ) ) );