Reg : How to make an expression

[sona_sa]

Hi Friends,

I need help for making an expression.

I have 12 types of Reason_Amount: These are following -

1.     Reason_RM

2.     Reason_Project

3.     Reason_PE

4.     Reason_Land

5.     Reason_Fin_Closure

6.     Reason_Row

7.     Reason_NOC

8.     Reason_ER

9.     Reason_DCLR

10.     Reason_Others

11.     Reason_LDC

12.     Reason_GC

I have also 2 more Amounts:

Net_Collectable_Amount and Total_Reason_Amount

Now I have to make an expression on these 14 fields:

Condition is like this:

1. If (Net_Collectable_Amount > Total_Reason_Amount) and any one Reason_Amount > 0, then replace that Net_Collectable_Amount to that

    Reason_Amount which have more then 0 (zero) amount.

2. Else If Check (Net_Collectable_Amount > Total_Reason_Amount) and Reason_RM = ' 0 ' and Reason_Fin_Closure = ‘ 0 ', then Add the difference of

     (Net_Collectable_Amount - Total_Reason_Amount ) in the behalf of max ( Another 10 Reason_Amount)

3. Else Check (Net_Collectable_Amount > Total_Reason_Amount ) and Reason_RM <> ' 0 ' and Reason_Fin_Closure <> ' 0 '  then Add the difference of (

     Net_Collectable_Amount - Total_Reason_Amount ) in the behalf of max( Another these two Reason_RM or Reason_Fin_Closure).

On these three condition I have to make a nested if expression.

For More Reference please find the attached image:

1. Total Collectable amount is greater than Total Reason Amount and Only the Reason_Project is greater then zero and the entire column is zero,

    then assign the Total Collectable amount to that particular Reason_Project.

2. Similarly Suppose Total Collectable amount is greater than Total Reason Amount and Reason_RM = ' 0 ' and Reason_Fin_Closure = '  0 ', Then difference

   of  Total Collectable amount and Total Reason Amount will assigned to max of another 10 Reason_Amount which have the max value except Reason_RM

   and Reason_Fin_Closure.

3. Similarly Suppose Total Collectable amount is greater than Total Reason Amount and Reason_RM <> ' 0 ' and Reason_Fin_Closure  <> '  0 ', Then

    difference of  Total Collectable amount and Total Reason Amount will assigned to max of  value either it is Reason_RM and Reason_Fin_Closure.

Please Find the attached  jpeg for refrence.

Please help on this.

Regards,

Akumar

[#2]

Hello Akumar,

I had short time to read this post but I believe that what you can do is first, nest the necessary ifs with the conditions you have, for example.

=if((Net_Collectable_Amount > Total_Reason_Amount) and (Reason_PE>0 or Reason..)

,Net_Collectable_Amount,  //ends first condition

//starts second condition

if(Net_Collectable_Amount > Total_Reason_Amount and Reason_RM=0 and Reason_Fin_Closure=0,vResult2,//ends second if

//starts third condition

if(...etc)

))

And so on.

After you do this, apply some "divide and conquer", create a variable that contains the final result calculation applied to all the conditions you have.

For example for your condition 1.

1. Total Collectable amount is greater than Total Reason Amount and Only the Reason_Project is greater then zero and the entire column is zero,

    then assign the Total Collectable amount to that particular Reason_Project.

create a variable such as vResult1 and put in there the formula for the desired result, do this until you complete all your conditions.

I'll try reviewing this question later when I have a little more time, hope this can help in the meantime.

Best

[sona_sa]

Hi Jorge,

I am explaining with example, Please go through the attached jpeg file.

On the top Net Collectable as per milestone - 1887.8, Reason Code Amount - 1509.064 and Net Collecable after reason - 447.7841

Net Collectable as per milestone = Reason Code Amount + Net Collecable after reason

Reason Code Amount = Land + LD Claim Amount + NOC + Projects + Early RR + Others + Invoice + PE

Net Collecable after reason = RM + Fin Closure

All these amount are coming on the behalf of some reason and at a time there is 2 reason amount will be availale (e.g - either LAND + Project or NOC + PE etc...) which is equal to the NET Collectable as per milestone.

Suppose the sum of these to reason amount is greater than NET Collectable as per milestone, Than we have to find which reason have max value and then assign to them whatever NET Collectable as per milestone amount is available.

Please try to resolve. Please revert to me for any clarification.

Thanks in advance.