Multi-row formula checking opening and closing dates run in sequence

[Brinker]

Hi,

I am trying to check that the transfer of accounts are in sequential order, an account can below to more than 1 person. The problem comes in that there can be multiple transfers to multiple owners, and I need to check that the open date is after the previous close date. If the close date is blank and the status is 'N' it is open.

I am having trouble when the transfer happens to more than one person and both accounts are open.

IF [Open] < [close]
then 'Pass'
elseif isnull([close])
and [open] > [Row-1:Close]

then 'Pass'
else 'Fail'
endif

[JamesCharnley]

Hi @Brinker,

Did you attach the right file? I can't seem to see the link between the question you've asked and the rows being generated in the workbook.

[Brinker]

Thanks @JamesCharnley, here is the correct file.

[JamesCharnley]

No problem @Brinker.

Do you have some kind of example desired output that you can share as well as a comparison? I can't quite follow exactly what the problem is still, and I'm guessing that since nobody has chimed in already then it's proving a little difficult to understand.

When you say that account can belong to more than one person, I'm assuming that means ID, but none of the account numbers are duplicated so I'm not tracking where that issue across owners is. Is the number different with different owners? 

Also you've said that if the close date is blank and the status is N it's open, but I'm assuming you meant the opposite? Otherwise there's no rows in your example that match that Open description. 

If we can clear a couple of the points up I don't think the multi-row should be too hard to configure!

[Brinker]

Hi @JamesCharnley Let me elaborate a bit ID relates to a product and account is the person, status 'N' is closed and Y is open.

For ID 3, we transfered from 2 people to 2 new people, so we need to check the previous closed date is < than the next opening date. If a row is open like ID 5 it is a valid open record. And ID 4 both closed the account so that is also valid. The problem is when there is more than one new open account. I can't figure the logic out.

 

[Brinker · Accepted solution]

Not sure if anyone will find it useful but I came to a solution, this code is what I implemented, so it is a bit different from the example dataset I posted, but the logic is there.

if [SOURCEAGREEMENTCODE] = [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'N' and [Row-1:ACCOUNTSTATUS] = 'N'
and [Row-1:CLOSEDDATES] < [COMMENCEMENTDATE] then 'PASS'

elseif [SOURCEAGREEMENTCODE] = [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'N' and [Row-1:ACCOUNTSTATUS] = 'Y'
and [COMMENCEMENTDATE] > [Row-1:CLOSEDDATES] then 'PASS'

elseif [SOURCEAGREEMENTCODE] = [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'Y' and [Row-1:ACCOUNTSTATUS] = 'Y'
and [COMMENCEMENTDATE] > [Row-1:CLOSEDDATES] then 'PASS'

elseif [SOURCEAGREEMENTCODE] = [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'N'
and [COMMENCEMENTDATE] = [Row-1:COMMENCEMENTDATE] then 'PASS'

elseif [SOURCEAGREEMENTCODE] != [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'N' then 'PASS'

elseif [SOURCEAGREEMENTCODE] != [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'Y'
and [COMMENCEMENTDATE] < [CLOSEDDATES] then 'PASS'

else 'FAIL'
endif