topic Expresions problem in QlikView

Expresions problem

harjedalskok — Mon, 12 Oct 2015 11:12:11 GMT

Hi,

We are trying to calculate a bonus for our salesmans,

where we based on there sales on every order, we take in consideration how much of the sales price is above minimum pricelist.

in script

(((SalesAmount) - ((minimumprice)+(shippingcost))) / ((minimumprice)+ (shippingcost))) as deviation

then we give them 7 % extra bonus on the part of the deviation that is above 12,5 %

sum(((deviation - '0,125') * salesamount) * '0,07')

This is working properly, but it gives the bonus above 25%

We only want to pay the extra bonus for when the deviation is between 12,5 % to 25 %

for example,

salesman has a order that minimum price is 80 000 and he sales the order for 105 000

with expression above he would get bonus for the 5000 that is above 25%.

Thx in advance.

André

swuehl — Mon, 12 Oct 2015 11:29:20 GMT

Maybe something like

sum((( rangemin( deviation, 0.25) - '0,125') * salesamount) * '0,07')

or even cutting off lower end:

sum((( rangemax(rangemin( deviation, 0.25) - 0.125),0) * salesamount) * '0,07')

harjedalskok — Mon, 12 Oct 2015 14:03:58 GMT

Hi,

Thank you for fast reply, ive tryied them both, and the above is working, the above give me a error on the comma and the zero after -0,125)

for now i have it in the script as an option:

if( (((SalesAmount) - ((minimumprice)+(shippingcost))) / ((minimumprice)+ (shippingcost))) >= '0,125', 'Yes', 'No' ) as Approved

harjedalskok — Mon, 12 Oct 2015 14:04:53 GMT

I ment the second one has an syntax error.

swuehl — Mon, 12 Oct 2015 14:10:36 GMT

I think some brackets need to be rearranged:

sum(( rangemax( (rangemin( deviation, 0.25) - 0.125) ,0) * salesamount) * 0.07)

harjedalskok — Mon, 12 Oct 2015 14:15:10 GMT

Thx Alot!

You saved my day!

/André