topic Re: Expressions Issue in QlikView

Expressions Issue

harjedalskok — Wed, 18 Mar 2015 10:01:25 GMT

Hi,

Im trying to create a expression that states the following

If the listprice divided by the salesprice is more or equal to 112.5 % then the amount above should be mutilplide by 1.30 if not then multipled by 1.0

ive started but got stucked pretty fast.

If(((Sum(lägstapris)/Sum(Försäljningspris))<=('1,125')), * '1.30' )

Im kinda in a stress in this matter.

Thx in advance

/André

Re: Expressions Issue

robert_mika — Wed, 18 Mar 2015 10:07:03 GMT

Try this:

If((Sum(lägstapris)/Sum(Försäljningspris))>=1.125,Sum(lägstapris)/Sum(Försäljningspris)) * 1.30,Sum(lägstapris)/Sum(Försäljningspris))

You do not need to multiply by 1 as will give you the same result.

Re: Expressions Issue

jyothish8807 — Wed, 18 Mar 2015 10:08:39 GMT

Hi Andre,

Is it what you are looking for?

IF((LP/SP)>='112.5%',((LP/SP)-1125)*1.30,(1125-(LP/SP))*1.0)

Regards

Re: Expressions Issue

aveeeeeee7en — Wed, 18 Mar 2015 10:08:41 GMT

Try this:

If(Num((Sum(lägstapris)/Sum(Försäljningspris)),'#,##0%')<='112.50%',(Sum(lägstapris)/Sum(Försäljningspris))*1.30,

(Sum(lägstapris)/Sum(Försäljningspris))*1.0)

Hope that works

Regards

Av7eN

Re: Expressions Issue

Wed, 18 Mar 2015 10:08:46 GMT

Hi,

=if((sum(listprice)/sum(salesprice))>=112.5,(sum(listprice)/sum(salesprice))*1.30,(sum(listprice)/sum(salesprice))*1.0)

Re: Expressions Issue

PrashantSangle — Wed, 18 Mar 2015 10:09:41 GMT

Hi,

Try

If((Sum(lägstapris)/Sum(Försäljningspris))>=1.125,

(Sum(lägstapris)/Sum(Försäljningspris))*1.30,

(Sum(lägstapris)/Sum(Försäljningspris))

)

regards

Re: Expressions Issue

jyothish8807 — Wed, 18 Mar 2015 10:11:34 GMT

I think I took your statement " amount above " in wrong sense.

IF(sum(LP)/sum(SP)>='112.5%',sum(LP)/sum(SP)*1.30,sum(LP)/sum(SP))

Regards

Re: Expressions Issue

Wed, 18 Mar 2015 10:15:17 GMT

Hi,

What do you mean by "amount above"? I am assuming it as sum(ListPrice)/sum(SalesPrice).

Could you please try using it:

if(

(sum(ListPrice)/sum(SalesPrice))*100 >=112.5,
(sum(ListPrice)/sum(SalesPrice))*1.3,

(sum(ListPrice)/sum(SalesPrice))

)

Re: Expressions Issue

prieper — Wed, 18 Mar 2015 10:17:55 GMT

lägstapris * (If(lägstapris/Försäljningspris <=1.125)), 1.30, 1))

should do (please check decimal-symbol to be either "." or ",".

HTH Peter

Re: Expressions Issue

harjedalskok — Wed, 18 Mar 2015 10:20:33 GMT

Hi,

Thx for fast response, im gonna try the above, just to clearify, its only the amount above 1,125 that should be multiplied, so the expression should give the result,

lets for say that the salesprice is 50000 the list price is (100%)40000 then the first (112,5 %) 45000 should be multiplied with 1.0 the rest (12,5 %) 5000 should be multiplied by 1.30 giving a result of 51500.

so the extra gain for the salemans bonus is 1500 since he sold the kitchen 25% above list price.

Hope this sorted some of the thoughts

Re: Expressions Issue

harjedalskok — Wed, 18 Mar 2015 10:21:49 GMT

this is a new bonus system that give the salesman 30% extra when they salesamount is 12,5 % above listprice.

Re: Expressions Issue

prieper — Wed, 18 Mar 2015 11:23:26 GMT

So the formula is

(SP - LP * 1.125) * 1.3?

Peter

Re: Expressions Issue

harjedalskok — Wed, 18 Mar 2015 11:32:16 GMT

No, since the difference between salesprice and listprice renders a percentage depending on that percentage some action will be taken, if the salesman have a percentage of 1,125 meaning he has sold for 12,5 % over the listprice, this doesent render anything extra for the salesman except the normal bonus, but if the sales would have been 1.25 % this means that the salesman should have an extra of 30 % on the amount above 1.125.

SP 50000 LP 40000 diffrence 10000 equal to 25 % above LP 12,5 % of the above results in 5000 that he should have an extra of 30 % on meaning that the salesman will get on that kitchen 1500 % on bonus base sales, since the kitchen in the bonus calculation will have a value of 51500 instead of the normal 50000.

Re: Expressions Issue

Roop — Wed, 18 Mar 2015 12:35:44 GMT

I think that this is what you are looking for ....

If(((Sum(lägstapris)/Sum(Försäljningspris)) <= 1.125,

Sum(Försäljningspris) + ((Sum(lägstapris) - Sum(Försäljningspris)) * 1.3,

Sum(Försäljningspris)

)