- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Return earliest date only for TRUE values
xx
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Return earliest date only for TRUE values
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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