sum with nested if

[#1]

Hi ,

I have below script in my expression

when i have one row code like ;

Sum(if(((TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153)) ,Bedehkar-Bestankar)/1000000) 

it's work fine But

when i have 2 row code like ;

Sum(if(((TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153)), 
if(((TotalCode=86 AND PortID=3304) or (TotalCode=88 AND PortID=3304) or (TotalCode=87 AND PortID=3304)),Bedehkar-Bestankar)/1000000))

it's not work and receive me 0.

i want my script work with multi row code .

can anyone help me

regards

hamide

[sunny_talwar]

Can you share an example? It is hard to understand what your requirement is just by looking at the code

[roger_stone]

The second IF is tested if the first IF is true. I think you need to review your logic.

[marcus_sommer]

Try something like this:

Sum(match(TotalCode, 86,87,88) AND match(PortID, 1153,3304),[Bedehkar-Bestankar])/1000000))

- Marcus

[Mark_Little]

HI,

Yer more details would be helpful. even if you explain what you want in English?

It seems at the moment you are saying

If statement 1 is true,

     Check that statement 2 is also true, then sum.

From look at that the logic it would be zero as your first statement returns values matching

(TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153)

So they can't then also match

(TotalCode=86 AND PortID=3304) or (TotalCode=88 AND PortID=3304) or (TotalCode=87 AND PortID=3304)

Maybe something like

Sum(if((TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153), 

      Bedehkar-Bestankar/1000000,

  if((TotalCode=86 AND PortID=3304) or (TotalCode=88 AND PortID=3304) or (TotalCode=87 AND PortID=3304),

  Bedehkar-Bestankar)/1000000))

Or

     Sum(if((TotalCode=86 AND PortID=1153) or (TotalCode=88 AND PortID=1153) or (TotalCode=87 AND PortID=1153) or (TotalCode=86 AND PortID=3304) or (TotalCode=88 AND PortID=3304) or (TotalCode=87 AND PortID=3304),

  Bedehkar-Bestankar/1000000)

[buzzy996]

ur 2'nd if condition is wrong,

use Marcus_Sommer suggestion,tht helps.