Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Creator

## Min Date based off another

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!

Labels (1)
• ### Application Development

1 Solution

Accepted Solutions
Specialist

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) )

6 Replies
Specialist

Try this:

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

Creator
Author

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

Specialist

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)

Creator
Author

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

Specialist

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) )

Creator
Author

That worked perfectly! Thank you so much!

Community Browser