Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
nburton78
Creator
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:

ordernumberMax540_DateMin545Date
11/1/20201/1/2020
22/1/20202/2/2020
33/1/20203/3/2020
44/1/20204/4/2020
55/1/20205/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)
1 Solution

Accepted Solutions
GaryGiles
Specialist
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) )

View solution in original post

6 Replies
GaryGiles
Specialist
Specialist

Try this:

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

nburton78
Creator
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.    

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

nburton78
Creator
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.  

 

nburton78_0-1624293989195.png

 

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

View solution in original post

nburton78
Creator
Creator
Author

That worked perfectly! Thank you so much!