2 Replies Latest reply: Oct 31, 2014 3:16 PM by Darrin Pilkington RSS

    Sum If - If expression is true then 1 and sum those values not working

    Darrin Pilkington

      In my app I am capturing the Average QA scores for agents and identifying if they Exceed, Meet or Need Improvement.

      The problem I am having is Totaling how many agents fall into each category. 

       

      The expression to identify where they fall:

      =if(avg(QAScore), < Only({<MetricID = {1} >} VariableValueBelow), 0,

      if(avg(QAScore) >= Only({<MetricID = {1} >} VariableValueExceeds), 2,

      if(isnull(avg(QAScore)), 0,

      1)))

       

      From the above I have broken out Below and Exceeds Figuring I could use the Count of Agents minus Below and Exceeds to find Meets:

      Below:       if(avg(QAScore) < Only({<MetricID = {1} >} VariableValueBelow), 1)

      Exceeds:   if(avg(QAScore) >= Only({<MetricID = {1} >} VariableValueExceeds), 1)

       

      NameAvgBelowMeetsExceedsAgents
      Dianna97.53%--11
      Gardner93.57%--11
      Michael88.54%---1
      Guadalupe88.33%---1
      Terry88.22%---1
      Sandra85.40%---1
      Kelley82.73%---1
      Reginald81.16%---1
      Pusey79.47%1--1

       

       

      I am trying to get a final chart like this.  # of agents Below, Meets and Exceeds.

      BelowMeetsExceeds
      162

       

      Thank you for you help.