After insert ,if(US_Cust='MYR',1/3, 1) the expression not more working

[paulyeo11]

Hi All

i Have the below expression work fine :-

'S$ ' &Num(

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1)) * days3)

+ sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1)) * days6) +

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1)) * days9) +

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1))*above12)+

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1)) * curren)

,'###,##0.00')

I try to adding one more currency for MYR :-

'S$ ' &Num(

sum(If( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1,if(US_Cust='MYR',1/3, 1))) * days3)+

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1,if(US_Cust='MYR',1/3, 1))) * days6) +

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1,if(US_Cust='MYR',1/3, 1))) * days9) +

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1,if(US_Cust='MYR',1/3, 1)))*above12)+

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1,if(US_Cust='MYR',1/3, 1))) * curren)

,'###,##0.00')

above expression return null value , can some one advise me where i go wrong ?

Paul

[Anonymous · Accepted solution]

your condition has one execution path to much

if (US_Cust='EU',1.5,1, if

if =EU then 1.5; if not EU then 1; when next if should be performed?

'S$ ' &Num(

sum(If( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1,if(US_Cust='MYR',1/3, 1))) * days3)+

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5, 1,if(US_Cust='MYR',1/3, 1))) * days6) +

[trdandamudi]

May be as below:

'S$ ' &Num(

sum(If( US_Cust='USD', 1.3,if(US_Cust='EU',1.5,if(US_Cust='MYR',1/3, 1))) * days3)+

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5,if(US_Cust='MYR',1/3, 1))) * days6) +

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5,if(US_Cust='MYR',1/3, 1))) * days9) +

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5,if(US_Cust='MYR',1/3, 1)))*above12)+

sum(If ( US_Cust='USD', 1.3,if(US_Cust='EU',1.5,if(US_Cust='MYR',1/3, 1))) * curren)

,'###,##0.00')

[gferran]

Paul,

i think the brakets are not ok. Look at this:

the second if has three options! I have worked with your first if sentence.

Regards,

Gabriel

[Anil_Babu_Samineni]

Try to write full expression instead of days3,6,9,12, curr

[paulyeo11]

Hi Rudolf

Thank you very much

PAul

[paulyeo11]

Hi Anil

I think you got the point , as now i need to more condition , i find it hard to handle , are you able to share the how to simplify the below expression :-

'S$ ' &Num(

sum(If ( US_Cust='USD', 1.3,If ( US_Cust='MYR', 1/3,if(US_Cust='EU',1.5, 1))) * days3)

+ sum(If ( US_Cust='USD', 1.3,If ( US_Cust='MYR', 1/3,if(US_Cust='EU',1.5, 1))) * days6) +

sum(If ( US_Cust='USD', 1.3,If ( US_Cust='MYR', 1/3,if(US_Cust='EU',1.5, 1))) * days9) +

sum(If ( US_Cust='USD', 1.3,If ( US_Cust='MYR', 1/3,if(US_Cust='EU',1.5, 1)))*above12)+

sum(If ( US_Cust='USD', 1.3,If ( US_Cust='MYR', 1/3,if(US_Cust='EU',1.5, 1))) * curren)

,'###,##0.00')

So that it is easy for me to manage.

Paul

[paulyeo11]

Hi Anil

I try and i get it :-

'S$ ' &Num(

sum(If ( US_Cust='USD', 1,If ( US_Cust='MYR', 1/3,if(US_Cust='EU',1.5, 1))) * curren+days3+days6+days9+above12)

,'###,##0.00')

Paul