Returning a value based on a threshold

[WcdSwami]

Hi everyone,

Qlikview newbie here. I am working on sales report in which a sales bonus must be calculated from a multiplier, which is based on a threshold from another table. Example of the data:

Name	Sales Target	Actual Sales	% of target	Bonus multiplier
A	100	103	103%	1.2
B	200	140	70%	0
C	300	400	133%	1.9
D	400	402	100.50%	1

"Bonus multiplier" is the field, for which I cannot figure out the expression. I suppose that logic should be is to take the value from "% of target", compare it against the threshold table below:

Threshold	Multiplier
0%	0
80%	0.5
85%	0.7
90%	0.8
95%	0.9
100%	1
101%	1.2
105%	1.3
110%	1.4
115%	1.6
120%	1.8
125%	1.9

and return multiplier value for the highest achieved threshold. However, I have no idea how to write this is expression. I thought of using a match function, but it would ignore the values that fall inbetween the thresholds.

Has anyone worked on this type of problem before?

Any help would be greatly appreciated,

Niklavs.

[fosuzuki]

If you want to do it on the script, you can use the IntervalMatch function.

Basicallly you setup the lower and upper intervals for each multiplier, and then mix it with the Sales data.

[WcdSwami]

@fosuzuki, thank you for the suggestion, I was not aware of the IntervalMatch function! However, in this case, the solution needs to be built within the table.

After further reading on the set analysis I found out that set analysis calculates only once per table, not once per row. Hence, if I understand correctly, its not possible to create a solution with set analysis and the only way how to implement this is in the load script?

[Saravanan_Desingh · Accepted solution]

Using IntervalMatch.

tab1:
LOAD * INLINE [
    Name, Sales Target, Actual Sales, % of target
    A, 100, 103, 103%
    B, 200, 140, 70%
    C, 300, 400, 133%
    D, 400, 402, 100.50%
];

tabTH:
LOAD * INLINE [
    Thmin, Thmax, Bonus Multiplier
    0%, 79%, 0
    80%, 84%, 0.5
    85%, 89%, 0.7
    90%, 94%, 0.8
    95%, 99%, 0.9
    100%, 101%, 1
    101%, 104%, 1.2
    105%, 109%, 1.3
    110%, 114%, 1.4
    115%, 119%, 1.6
    120%, 124%, 1.8
    125%, 200%, 1.9
];

Inner Join
IntervalMatch([% of target])
LOAD Thmin, Thmax
Resident tabTH;

[Saravanan_Desingh]

[WcdSwami]

Thank you for the input, I think IntervalMatch is the closest we can get to a "solution" in this case.