Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Assigning Points based on the value between 2 metrics.

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.

4 Replies
Highlighted
MVP
MVP

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

So, do you want to assign 5 or 1?

Highlighted
Specialist
Specialist

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

Highlighted
MVP
MVP

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

Highlighted
Specialist
Specialist

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