2 Replies Latest reply on Oct 22, 2018 7:25 AM by Sukanya Kadam

    Division and lookup in the same formula

    Sukanya Kadam



      I have a calculation called rate where rate = numerator/denominator. Then I have another data source, where we need 3 columns: Score from, Score to and rank. The idea is if the rate falls anywhere between the score to and score from values, bring in the corresponding rank.


      I tried the formula:

      if [MSX_IP_OUTPUT (MS_INSIGHT)].[% Rating] >= [Score From]

      and [MSX_IP_OUTPUT (MS_INSIGHT)].[% Rating] <=[Score To]

      then [Rank]



      But I get the error "cannot mix aggregate and non-aggregate arguments with this function"


      I also tried to divide in the formula:

      if [MSX_IP_OUTPUT (MS_INSIGHT)].[Pat Sat Num]/[MSX_IP_OUTPUT (MS_INSIGHT)].[Pat Sat Denom]>= [Score From]

      and [MSX_IP_OUTPUT (MS_INSIGHT)].[Pat Sat Num]/[MSX_IP_OUTPUT (MS_INSIGHT)].[Pat Sat Denom] <= [Score To]

      THEN [Rank]



      The structure of the rank table is like this:


      RankRank PeriodRank Period DtRank QuarterRank Question IdRank YearScore FromScore To
      12017066/1/2017 0:0020172CMS_37CL2017047.2
      22017066/1/2017 0:0020172CMS_37CL201747.349.3
      32017066/1/2017 0:0020172CMS_37CL201749.450.2
      42017066/1/2017 0:0020172CMS_37CL201750.351
      52017066/1/2017 0:0020172CMS_37CL201751.151.7
      62017066/1/2017 0:0020172CMS_37CL201751.852.3
      72017066/1/2017 0:0020172CMS_37CL201752.452.8
      82017066/1/2017 0:0020172CMS_37CL201752.953.7
      92017066/1/2017 0:0020172CMS_37CL201753.854.1
      102017066/1/2017 0:0020172CMS_37CL201754.254.5
      112017066/1/2017 0:0020172CMS_37CL201754.654.9
      122017066/1/2017 0:0020172CMS_37CL20175555.4
      132017066/1/2017 0:0020172CMS_37CL201755.555.8
      142017066/1/2017 0:0020172CMS_37CL201755.956.2
      152017066/1/2017 0:0020172CMS_37CL201756.356.6
      162017066/1/2017 0:0020172CMS_37CL201756.756.9
      172017066/1/2017 0:0020172CMS_37CL20175757.2
      182017066/1/2017 0:0020172CMS_37CL201757.357.4
      192017066/1/2017 0:0020172CMS_37CL201757.557.7
      202017066/1/2017 0:0020172CMS_37CL201757.858


      Thank you for your help!!!