1 Reply Latest reply on Jun 19, 2018 1:10 PM by Jim Dehner

    Aggregate Vs Non Aggregate - Cannot Mix aggregate and non-aggregate arguments with this function?

    Ocieka Bakou

      So the idea is that I am trying to calculate Turn around time for our Service Desk Tickets. I first calculated Date Opened - Date Closed with the following Function ---> DATEDIFF('day',[Created Date],[Closed Date],'monday'), I then wanted to to find the Average with the AVG function.

       

      Now I am trying to do an if function -- The idea is if the priority is identified as High Medium or  Low there are specific criteria's  for each of them, which I try to show here.  and I get the "Cannot Mix aggregate and non-aggregate arguments with this function"

       

      Any other way to do the following?

       

      If AVG([Test])>2 AND[Priority]="High" THEN "bad"

      ELSEIF  AVG([Test]) <= 2 AND [Priority]="High" THEN "good"

      ELSEIF  AVG([Test])>5 AND [Priority]="Medium" THEN "bad"

      ELSEIF  AVG([Test])<=5 AND [Priority]="Medium" THEN "good"

      ELSEIF  AVG([Test])>7 AND [Priority]="Low" THEN "bad"

      ELSEIF  AVG([Test])<=7 AND [Priority]="Low" THEN "good"

      ELSE "null"

      END

        • 1. Re: Aggregate Vs Non Aggregate - Cannot Mix aggregate and non-aggregate arguments with this function?
          Jim Dehner

          Hi

          Once one of the fitled in a formula is aggregated then all fields need to aggregated - AVG is an Aggregate so you need to wrap Priority in an aggregating expression like Min(), Max() or Attr() in all cases

           

           

          If AVG([Test])>2 AND   attr([Priority]  ) ="High" THEN "bad"

          ELSEIF  AVG([Test]) <= 2 AND attr([Priority]  )="High" THEN "good"

          ELSEIF  AVG([Test])>5 AND attr([Priority]  )="Medium" THEN "bad"

          ELSEIF  AVG([Test])<=5 AND attr([Priority]  )="Medium" THEN "good"

          ELSEIF  AVG([Test])>7 AND attr([Priority]  )="Low" THEN "bad"

          ELSEIF  AVG([Test])<=7 AND attr([Priority]  )="Low" THEN "good"

          ELSE "null"

          END

           

          jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.