topic Re: Min Date based off another in QlikView

Min Date based off another

nburton78 — Sat, 19 Jun 2021 14:44:49 GMT

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!

Re: Min Date based off another

GaryGiles — Sun, 20 Jun 2021 12:15:49 GMT

Try this:

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

Re: Min Date based off another

nburton78 — Sun, 20 Jun 2021 23:34:14 GMT

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

Re: Min Date based off another

GaryGiles — Mon, 21 Jun 2021 11:14:21 GMT

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)

Re: Min Date based off another

nburton78 — Mon, 21 Jun 2021 16:46:59 GMT

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

Re: Min Date based off another

GaryGiles — Mon, 21 Jun 2021 18:36:08 GMT

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

Re: Min Date based off another

nburton78 — Mon, 21 Jun 2021 19:47:05 GMT

That worked perfectly! Thank you so much!