5 Replies Latest reply: Jul 7, 2017 4:35 PM by Cassandra Baqir RSS

    AGGR Syntax with IF statement and FirstSortedValue Help

    Cassandra Baqir

      I have a requirement to exclude any DEFECT_ID where:

      Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE)<1

      but I still see many showing up with less than 1 day. What am I missing?

       

      Select DEFECT STATUSES: New and Approved

       

       

      My expression:

       

       

      Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(
      Aggr(
       
      Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}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))),MONTH_YEAR, DEFECT_ID))

       

      7-7-2017 10-55-01 AM.jpg

       

      TIA!

        • Re: AGGR Syntax with IF statement and FirstSortedValue Help
          Sunny Talwar

          May be you are looking for this

           

          If(Sum(Aggr(

          Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}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) > 1,

          Sum(Aggr(

          Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}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))

            • Re: AGGR Syntax with IF statement and FirstSortedValue Help
              Cassandra Baqir

              Thanks, Sunny. Let me dissect to make sure I understand...

               

              I want to exclude those defects where the difference between the first occurrence of both selected statuses is less than 1, not where the average is less than 1, so, think I need to change the First part from this:

               

              If(Sum(Aggr(

              Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}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) > 1,

               

              to this:

               

              If(Sum(Aggr(

              Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))"}>}DISTINCT DEFECT_ID) > 1,

               

              Does it still make sense?

               

              If(Sum(Aggr(
              Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))
              > 1,
              Sum(Aggr(
              Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}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))