topic Re: Assigning Points based on the value between 2 metrics. in QlikView

Assigning Points based on the value between 2 metrics.

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Assigning Points based on the value between 2 metrics.

swuehl — Thu, 01 Sep 2016 20:18:30 GMT

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

So, do you want to assign 5 or 1?

Re: Assigning Points based on the value between 2 metrics.

rupamjyotidas — Thu, 01 Sep 2016 20:20:44 GMT

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

Re: Assigning Points based on the value between 2 metrics.

swuehl — Thu, 01 Sep 2016 20:24:50 GMT

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

Re: Assigning Points based on the value between 2 metrics.

johnca — Thu, 01 Sep 2016 20:28:58 GMT

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