601705_en-US

liliconcom

Return earliest date only for TRUE values

 

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

Data Blending and CleanupRe: Return earliest date only for TRUE values

Hi,

You are mostly there. Try the following:

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

Best,

TS

Re: Return earliest date only for TRUE values

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

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

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

Obviously Jim's approach is the way to go. I could not get the missings to work out the way i wanted; I was evaluating them out of order and wound up with this convoluted, embarrassing mess. Please disregard this solution. I'd delete it, but don't know how to do so.

 

Cheers,

Brady

 

 

Re: Return earliest date only for TRUE values

  xx

Re: Return earliest date only for TRUE values

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

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