Assigning Points based on the value between 2 metrics.

[#1]

After all the joins and resident calc  my final table looks as below.

Table 1 :

ID  COST     Item           Target

1    40000      20                20000

2    50000      60                20000

3    60000      30                20000

Target is static number.

I have to compare cost/Item to target  amount and based on the percentage I have to assign score as below.

If COST/Item is <= Target then 5

if COST/Item is  0 to 10% above Target and 25% to 50% below Target then 3

if COST/Item is  10% above Target or 50% below Target then 1 .

How can I put this in the script.I tried as below ...but it is not working..

If(Round(num(100*(1-([Target]/(([Cost])/Item))),'#,##0')) <=0,5,

If(Round(num(100*(1-([Target]/(([Cost])/Item))),'#,##0'))>0

  and Round(num(100*(1-([Target]/(([Cost])/Item))),'#,##0'))<=10 ,3,

   If(Round(num(100*(1-([Target]/(([Cost])/Item))),'#,##0'))>25

    and Round(num(100*(1-([Target]/(([Cost])/Item))),'#,##0'))<=50 ,3, 1))) as [Score].

Thanks much.

[swuehl]

If COST/Item is like 50% of Target, it's also <= Target, right?

So, do you want to assign 5 or 1?

[rupamjyotidas]

Can you check the logic, as I feel 'If COST/Item is <= Target then 5' will put all score to 5. Or if you can explain a little more. A example file would be helpful

[swuehl]

Maybe try like

If( (COST/Item <= 1.1 *Target and Cost/Item > Target) or (COST/Item <= 0.75*Target and COST/Item >= 0.5*Target), 3,

     If(COST/Item < 0.5*Target or COST/Item >= 1.1*Target, 1,

          If(COST/Item <= Target, 5)

     )

)          as Score

[johnca]

Hi Pavana, it's not clear to me why it is more complicated than it needs to be, but this gets what you want in the script (or straight table with ID dimension);

If(Target/Cost/Item <= 0, 5,

  If((Target/Cost/Item > 0 And Target/Cost/Item <= 10), 3,

  If((Target/Cost/Item > 25 And Target/Cost/Item <= 50),3,1))) as Score

I tried adding the same parenthesis in the 2nd two buckets to your script but I too didn't get it working. It DID calculate the bucket right though...all the values were negative so 5 was correct.

HTH,

John