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

# Division and lookup in the same formula

Hello,

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]

END

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]

END

The structure of the rank table is like this:

 Rank Rank Period Rank Period Dt Rank Quarter Rank Question Id Rank Year Score From Score To 1 201706 6/1/2017 0:00 20172 CMS_37CL 2017 0 47.2 2 201706 6/1/2017 0:00 20172 CMS_37CL 2017 47.3 49.3 3 201706 6/1/2017 0:00 20172 CMS_37CL 2017 49.4 50.2 4 201706 6/1/2017 0:00 20172 CMS_37CL 2017 50.3 51 5 201706 6/1/2017 0:00 20172 CMS_37CL 2017 51.1 51.7 6 201706 6/1/2017 0:00 20172 CMS_37CL 2017 51.8 52.3 7 201706 6/1/2017 0:00 20172 CMS_37CL 2017 52.4 52.8 8 201706 6/1/2017 0:00 20172 CMS_37CL 2017 52.9 53.7 9 201706 6/1/2017 0:00 20172 CMS_37CL 2017 53.8 54.1 10 201706 6/1/2017 0:00 20172 CMS_37CL 2017 54.2 54.5 11 201706 6/1/2017 0:00 20172 CMS_37CL 2017 54.6 54.9 12 201706 6/1/2017 0:00 20172 CMS_37CL 2017 55 55.4 13 201706 6/1/2017 0:00 20172 CMS_37CL 2017 55.5 55.8 14 201706 6/1/2017 0:00 20172 CMS_37CL 2017 55.9 56.2 15 201706 6/1/2017 0:00 20172 CMS_37CL 2017 56.3 56.6 16 201706 6/1/2017 0:00 20172 CMS_37CL 2017 56.7 56.9 17 201706 6/1/2017 0:00 20172 CMS_37CL 2017 57 57.2 18 201706 6/1/2017 0:00 20172 CMS_37CL 2017 57.3 57.4 19 201706 6/1/2017 0:00 20172 CMS_37CL 2017 57.5 57.7 20 201706 6/1/2017 0:00 20172 CMS_37CL 2017 57.8 58

• ###### 1. Re: Division and lookup in the same formula

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

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

then [Rank]

END

• ###### 2. Re: Division and lookup in the same formula

Thanks Deepak.

I tried this formula, but I get the error “Argument to ATTR (an aggregate function) is already an aggregation, and cannot be further aggregated.”