5 Replies Latest reply: Jul 6, 2017 4:26 PM by Cassandra Baqir RSS

    Duration from First Occurence

    Cassandra Baqir

      Select DEFECT_ID = 1073 and Statuses of New and Approved.

       

      For this defect, the start date for new is 9/23/13 and there are two instances of Approved... one with a start date of 11/11/2013 and one with 11/25/13.

       

      Right now the expression is showing me 63 days which is 11/25/13-9/23/13.

       

      Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(
      Aggr(
      Max(DEFECT_STATUS_START_DATE)- Min(DEFECT_STATUS_START_DATE)
      ,
      DEFECT_ID)
      )

      /
      Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID))),FISCAL_YEAR, DEFECT_ID))

       

      I only want the first occurrence of whichever Statuses (DEFECT_STATUS_MAP) are selected. In this example, I would want to see 49 days which is 11/11/2013-9/23/13.

       

      TIA!

       

      stalwar1