0 Replies Latest reply: Apr 12, 2017 11:31 AM by David Brenner RSS

    Adding multiple KPI calculations together which contain long if statements

    David Brenner

      I have table which contains a value based upon a percent range.  I was able to calculate the percent performance and assign performance points for each individual user in the load editor.  I have no problem displaying this value when an individual user is selected.  I want to calculate and display the performance points in the same display based upon the performance points of all users when no single user is selected.  The average of all users does not give me the results I need.  I have attempted to calculate the performance points within the KPI calculation, but ran into problem due to the multiple if statements.  The end goal is to add multiple performance points together to obtain an overall performance score.  This the first step.

      Here is what I have done thus far.

       

      USERNUMERATOR_MET_COUNTDENOMINATOR_COUNTPERCENT_PERFORMANCEPERFORMANCE_POINTS
      A185203.9110
      B448535.849
      C142241.606
      D394456.869
      E333359.9310

       

      Here is what I want to see in  KPI.

       

      When user A is selected:  10

      When user B is selected:  9

      etc...

       

      When all user as selected (no filter applied) then the value should be 9 based upon the a PERCENT_PERFORMANCE of .83 (sum NUMERATOR_MET_COUNT / sum DENOMINATOR_COUNT).  Here is the code I entered in the load editor.

      ...

      NUMERATOR_MET_COUNT / DENOMINATOR_COUNT AS [PERCENT_PERFORMANCE]

      IF([PERCENT_PERFORMANCE]>= .01 AND [PERCENT_PERFORMANCE] <.11 , '1',

          IF([PERCENT_PERFORMANCE]>=.11 AND [PERCENT_PERFORMANCE]<.21, '2',

          IF([PERCENT_PERFORMANCE]>=.21 AND [PERCENT_PERFORMANCE]<.31, '3',

          IF([PERCENT_PERFORMANCE]>=.31 AND [PERCENT_PERFORMANCE]<.41, '4',

          IF([PERCENT_PERFORMANCE]>=.41 AND [PERCENT_PERFORMANCE]<.51, '5',

          IF([PERCENT_PERFORMANCE]>=.51 AND [PERCENT_PERFORMANCE]<.61, '6',

          IF([PERCENT_PERFORMANCE]>=.61 AND [PERCENT_PERFORMANCE]<.71, '7',

          IF([PERCENT_PERFORMANCE]>=.71 AND [PERCENT_PERFORMANCE]<.81, '8',

          IF([PERCENT_PERFORMANCE]>=.81 AND [PERCENT_PERFORMANCE]<.91, '9',

              IF([PERCENT_PERFORMANCE]>=.91, '10', 0)))))))))) AS [PERFORMANCE_POINTS]

      ...