Min Date based off another

[nburton78]

I'm looking at order statuses and the dates the orders were updated to those statuses.  The statuses are 540 and 545.  

I have 2 tables, one with the max date an order hit a 540 status, the second with all the days an order was at 545 status.  What I'm trying to calculate is the 545 date that immediately follows the max545 status date.  like this:

ordernumber	Max540_Date	Min545Date
1	1/1/2020	1/1/2020
2	2/1/2020	2/2/2020
3	3/1/2020	3/3/2020
4	4/1/2020	4/4/2020
5	5/1/2020	5/5/2020

I'm trying to do this in a straight table

This is the formula that I'm using  to calculate the 3rd column but it just gives me "-". 

"Min({<Status={'545'},Update_Date={">=Max540Date"}>}Update_Date)" and it wont work! 

[GaryGiles]

Try this:

=Min({<Status={'545'},Update_Date={"=Update_Date>=Max540Date"}>}Update_Date)

[nburton78]

Doesn't work when all the data was loaded, it only worked on about 150 lines instead of 480K lines I was expecting.    

[GaryGiles]

We need to use the ordernumber as the field in the set analysis instead of Updated_Date.  

Min({<NextStatusCode={545},ordernumber={"=Updated_Date>=Max540Date"}>} Updated_Date)

[nburton78]

Its calculating now and its really close, however if there is more than one updated date at 545 status its not returning a value.  

[GaryGiles · Accepted solution]

Not sure my initial approach is the right way to go.  The following seemed to handle the scenarios with more than one updated date.  Was trying to avoid an if statement, but sometimes it is unavoidable:

Min({<NextStatusCode={545}>} if(Updated_Date>=Max540Date,Updated_Date) )

[nburton78]

That worked perfectly! Thank you so much!