Sum IF fieldvalue=fieldvalue then subtract from the right fieldvalue

[ontoit7331]

Hey Experts,

Im new to qlikview but hopefully I'll get better at it soon.

Is there by anychance that you can get this to work?:

Field1,Field2:

A,1

B,2

C,3

C,2

C,1

Total:9

field3,field4

A,100

B,200

C,400

i wanna sum all field2s depending on Field1

then i wanna subtract field2 from field4 depending if the field1=field3.

All this above without linking them together(it will get a loop because all of this is linked by dates.

I got it to work partly. Just that when i use this field2-field4 it subtract the total amount of  field2 and subtract it from each field3/4

makes the table look like this:

A,91

B,191

C,391

instead of this:

A,99

B,198

C,394

Hopefully someone know how to do this.

[lironbaram · Accepted solution]

hi 

this expression will work for you :

Sum(field4)- sum(if(field3=Field1,[Field2]))

but in general when you take into account the performance of the model , you'll be better serve to connect the tables 

even with a complex key that include the dates

[ontoit7331]

Hey,

I already use that expression and it subtract the total amount of field2 with every field4.
Maybe its the only way? To find a way to make a key in the script?

[lironbaram]

hi 

this expression works  exactly as you want

i tested it in an app see below screen shot

also attach is a qlikview model