0 Replies Latest reply: Nov 24, 2015 4:36 AM by danny rock RSS

    Firstsortedvalue with calculated date range

    danny rock

      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.

       

      CustomerSQNC_NOOrderORDER_LEVEL3StatusDate
      111251234FirstCreate1 Jan 12:00
      111251235Transit1 Jan 18:00
      111251236Prvsng5 Jan 17:00
      111251237LastCmplt6 Jan 06:00
      1112203000FirstCreate10 Feb 10:00
      1112203000LastCmplt10 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