Multi row Formula to identify max closed date

Hi All

The attached is a snippet of my whole dataset which has several accounts for the same id, what i'm trying to achieve is get a field that lets me know the latest closing date for all the accounts in the same ID group prior to the account in question's opened date where the Relationship status = returning

~Thanks in advance for the help

Screenshot 2024-01-18 155327.png

Workflow

Date Time

Help

Join

Preparation

Accepted answers

CoG

Here is a workflow that I believe does what you're looking for (as a note include actual tables with sample input is far more helpful than images of data):

Hope this helps! Happy Solving!!!

Sample Workflow.yxmd

All comments

ChrisTX

Can you provide expected output? The requirement isn't clear for: how to determine "the account in question's opened date".

Chris

Gabocorreia95

Hi ChrisTX

Apologies, so for the example provided the new column would only show the latest closed date for account 858002328 and 85003018 as their Relationship status at opened date is "returning"

The new column for 85002328 would show 2021/01/29 as that's the latest closed date prior to 85002328 open date which is 2021/02/06

For 85003018 The new column would show 2022/12/07 as that's the latest closed date prior to 85003018 open date which is 2024/01/04

Hopefully the above makes sense.

Thanks in advance for your help

Raj

please provide some clarity on requirement as it is difficult to understand the required output.
are you looking for ID opening date and latest closing date for "returning" or is there something else.

are you looking for this,
ID Opening Closing
316 2015-10-09 2024-01-18

Gabocorreia95

Hi Raj

Basically I'm looking for the latest closed date of the accounts in the same ID group prior to the open date of the accounts where the Relationship status at opened date is equal to returning.

Please refer to the example I shared on my previous response, hopefully that will help clarify my question.

Many thanks

ChrisTX

Can you provide sample input data and expected output using the Table format in this post?

Gabocorreia95

Hi ChrisTX

Please find attached an example of the desired output

Many thanks

Screenshot 2024-01-18 164952.png

CoG

Here is a workflow that I believe does what you're looking for (as a note include actual tables with sample input is far more helpful than images of data):

Hope this helps! Happy Solving!!!

Sample Workflow.yxmd

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335