hello all,
hoping someone can help me with this problem; from a list of Orders for a customer, I am trying to get the latest order from a date range from created date to created date plus vDays.
e.g,
For Orders that were created on 1st Jan, what are their statuses 2 days later? In this table, 2 days later, it would be Transit.
| Customer | SQNC_NO | Order | ORDER_LEVEL3 | Status | Date |
|---|
| 1112 | 5 | 1234 | First | Create | 1 Jan 12:00 |
| 1112 | 5 | 1235 | | Transit | 1 Jan 18:00 |
| 1112 | 5 | 1236 | | Prvsng | 5 Jan 17:00 |
| 1112 | 5 | 1237 | Last | Cmplt | 6 Jan 06:00 |
| 1112 | 20 | 3000 | First | Create | 10 Feb 10:00 |
| 1112 | 20 | 3000 | Last | Cmplt | 10 Feb 10:00 |
I began my expression by making sure I obtained the firstsorted date in desc which in the above example would have returned 1 Jan 18:00 using the following expression
Timestamp(aggr((FirstSortedValue({$<DATE={'<$(=timestamp(max({<ORDER_LEVEL ={"First"}>} ORD_CRTD_DT)+$(vDays)))'}>}ORDER_CREATED_DATE,-ORDER_CREATED_DATE)),SQNC_NO))
This expression only works when the customer or sqnc_no is selected, it pick the latest date when nothing is selected and basically ignores the date range in the set analysis. Bit of a doozy, but love to hear some advice. Thanks
