Firstsortedvalue with calculated date range

Anonymous — Tue, 24 Nov 2015 09:36:45 GMT

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

topic Firstsortedvalue with calculated date range in Scalability

Firstsortedvalue with calculated date range