topic Re: Returning a value based on a threshold in QlikView

Returning a value based on a threshold

WcdSwami — Wed, 10 Jun 2020 07:40:05 GMT

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.

Re: Returning a value based on a threshold

fosuzuki — Wed, 10 Jun 2020 08:28:09 GMT

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.

Re: Returning a value based on a threshold

WcdSwami — Thu, 11 Jun 2020 08:55:19 GMT

@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?

Re: Returning a value based on a threshold

Saravanan_Desingh — Thu, 11 Jun 2020 21:40:36 GMT

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;

Re: Returning a value based on a threshold

Saravanan_Desingh — Thu, 11 Jun 2020 21:42:30 GMT

Re: Returning a value based on a threshold

WcdSwami — Fri, 12 Jun 2020 06:32:24 GMT

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