topic Re: Return earliest date only for TRUE values in Discussions

Return earliest date only for TRUE values

paulp — Thu, 08 Jun 2023 16:35:26 GMT

Hello, I am creating a column formula that works half-right. Using a formula as I don't know JSL.

For each Account grouping ==> return the earliest date for that Account

IF the Capture value ="TRUE".

ELSE leave a blank.

Some Accounts don't have any TRUE values and should return nothing but missing values.

JMP® Pro 17.0.0 (622753)

Suggestions? thanks, Paul

Re: Return earliest date only for TRUE values

Thierry_S — Thu, 16 Feb 2023 05:52:43 GMT

Hi,

You are mostly there. Try the following:

If (:Capture == "TRUE", Col Minimum (:Date, :Account, :Capture), .)

Best,

Re: Return earliest date only for TRUE values

paulp — Thu, 16 Feb 2023 15:46:58 GMT

Thierry, thank you for your reply. I mis-specified my request, and your formula works for my misspecification. Below are screenshots of the logic I am looking for, and the associated small data set is attached.

Corrected request -

1. Within an Account, find the Earliest Date that has Capture = TRUE.

==> maybe this Date is stored in a temporary variable?

2. Fill in this Earliest Date for every row of this Account.

3. If the Account never had a historical TRUE, leave every row blank.

==> signals that this Account never had any success.

Example of an Account that has Capture = TRUE in its history.

Example of an Account that never has Capture = TRUE in its history.

Re: Return earliest date only for TRUE values

txnelson — Thu, 16 Feb 2023 16:05:06 GMT

Given your abbreviated sample table, here is the formula that should work

Col Min( If( :Capture == "TRUE", :Date, . ), :Account )

Re: Return earliest date only for TRUE values

paulp — Thu, 16 Feb 2023 16:26:47 GMT

Thank you - I have tested your solution on a handful of accounts, and so far, it is working perfectly. regards, Paul

Re: Return earliest date only for TRUE values

brady_brady — Thu, 16 Feb 2023 16:44:45 GMT

Re: Return earliest date only for TRUE values

brady_brady — Thu, 16 Feb 2023 16:45:53 GMT

I've deleted the reply (well, basically. I don't think you can delete but you can make the comment "xx") I originally made as Jim's is the way to go--

Cheers,

Brady

Re: Return earliest date only for TRUE values

P_Bartell — Thu, 16 Feb 2023 17:00:24 GMT

So I'm not the only one that has tried to delete a reply? Maybe a suggestion for the Community Discussion Forum web gurus? On more than one occasion for one reason or another I've wanted to delete a reply and couldn't figure out how to do it? But I like your 'xx' workaround. I'll remember this for future editorial work. Hope all is well.

Re: Return earliest date only for TRUE values

paulp — Thu, 16 Feb 2023 17:48:21 GMT

Brady, I appreciate your willingness to give it a try. Your proposed logic was a novel approach, maybe it will spark something I can use in the future. regards, Paul