Getting a Value from a Table with an Expression

[#1]

Hi

I am a little new to Qlikview and I have a question.

In my Model I am Calculating a Worker productivity %

it is based on the amount of Goods that worker received multiply by a factor based on the Type of goods

divided by his Target

So the Expression to get the Percent goes like this

Sum(Qty*Factor)/MonthlyUnits

Now I need to calculate the Bonus this worker will get each month

this bonus is based on another table

that links the Level of the Worker (WorkerGrade) with the Section of the Worker (WorkerGroup)

<both are linked to the Worker Directly and both are already there on the other tables)

and then it give me a Table of how much productivity Percent is worth how much 

so the Incentive (bonus) table will be something like this

Level | Group | Target | Amount

1      | 20       | 105%  | 200

1      | 20       | 110%  | 300

1      | 20       | 120     | 700

1      | 30       | 110%  | 200

2      | 30       | 120%  | 1000

Now what I need is in the Table

that shows the Productivity % of a Worker in that month

to also show How much bonus it needs to get

so I need to check if the Result of my expression (Sum(Qty*Factor)/MonthlyUnits)

is equal or more then the Target

and if it is, then to bring the Amount that is linked to that Target

* I can create a little different and make the Amount culumative if it will make it easier for a solution

Level | Group | Target | Amount

1      | 20       | 105%  | 200

1      | 20       | 110%  | 100

1      | 20       | 120%  | 400

and then if it is more then 105% he will get 200

and if it is more then 110% he will get another 100

and add another 400 for getting 120%

which come to the 700 of the previous example

I don't mind using either of those tables (though I prefer the first example as it is how the Client is using it at the moment)

would appreciate help

[somenathroy]

Can you pls attach a sample qvw file.

Regards,

Som

[#3]

Sure

I have added one with filter on a single worker

so that I will be able to supply an example as well

The Line i need to update is the Bonus line

The Result I'm expecting to see is -

have a value of 0 for Nov-2012, Jan & Feb of 2013

have value of 700 for Mar, May, Jul & Aug of 2013

have a value of 200 for Apr and Jun of 2013

The Incentive table at the moment