How to meet different conditions

[nevilledhamsiri]

Dear Experts!

Same data presented already but I am yet to receive a reply. Hence I thought of limiting some part of data & hope to resolve one after the other with your knowledge!

As per the data shown please propose expressions on following requirements!

1:If the class is MOTOR & achievement is 75% or more, incentive payable for 1st 75% is Rs 2000/=

2 If the class is motor & achievement is >75%<100%, each points should carry 150 per point. eg if the achievement if 87%, amount payable is (87-75=12*150), if 100%, (100-75=25*150) etc.

3 If the class is Non motor & achievement is more than 100%, each points should carry Rs 200/=. Eg, if the achievement is 105%, amount payable is (105-100=5*200) etc.

Above three levels involve in calculation of incentive payable. Please propose 4 expressions to calculate the same. 1st one for 75% achievement, 2nd being for 75%100% achievement, 3rd being for >100% & last being for addition of all three.

Thanks in advance!

Neville

[nevilledhamsiri]

Please make a note that above calculations need to be only for Motor class. By an error mentioned as Non Motor under level 3 to be replaced with Motor class.

Sorry for the inconvinence caused.

Neville

[Anil_Babu_Samineni]

You can do the calculated formula. But, I am not followed you as needed

May be this?

If(Class = 'motor' and ([Achi(%) Expr] >= 0.75 and [Achi(%) Expr] <= 1, ([Expr1 for 87] - [Expr for 75]) * 150,

If(Class = 'motor' and ([Achi(%) Expr] >= 0.75 and [Achi(%) Expr] <= 1, ([Expr1 for 87] - [Expr for 75]) * 150,

If(Class = 'motor' and ([Achi(%) Expr] >= 0.75 and [Achi(%) Expr] <= 1, ([Expr1 for 87] - [Expr for 75]) * 150,

.......

[nevilledhamsiri]

Dear Anil,

Thanks for your reply, I am trying to build u this under three levels, 1st level is for 75% achievement & once I applied achievement expression like one below, error comes. Please refer this expression & correct me if I have gone wrong!

Regards

Neville

IF(Class='MOTOR' AND Sum ([ACT-CY])/Sum (BUD)*100>=75,2000,0)

[Anil_Babu_Samineni]

Where you are going to build this? Can you show the image or try this? This should work

IF(Class='MOTOR' and ((Sum ([ACT-CY])/Sum (BUD))*100) >= 75,2000,0)

[nevilledhamsiri · Accepted solution]

Dear Anil,

Thanks a lot. It works well. One more assistance is sought from you. Here I have completed all three levels & the total payable. There is a another condition which comes in to play when it comes to making the payment. That is when one class gets a negative growth even all other conditions are met, incentives are not able to be released. Eg in the Month of August there is a negative growth of 6% but the growth in total (Motor + Non motor) get a positive growth of 9% (refer the table)

So my requirement is, if there is a negative growth that payable amount to be highlighted as not releasble. But if the total growth gets positive as mentioned above it should be highlighted as releasable

Thanks

Neville

[Anil_Babu_Samineni]

Looks like you are getting Highlight BG for <0 values? And what you are assist you to do for +ve growth.. I believe you need 2 consition for BG color which is exist for -ve values and non negative values