topic Re: Sum IF fieldvalue=fieldvalue then subtract from the right fieldvalue in QlikView

Sum IF fieldvalue=fieldvalue then subtract from the right fieldvalue

ontoit7331 — Tue, 23 Apr 2019 07:47:03 GMT

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.

Re: Sum IF fieldvalue=fieldvalue then subtract from the right fieldvalue

lironbaram — Tue, 23 Apr 2019 08:01:36 GMT

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

Re: Sum IF fieldvalue=fieldvalue then subtract from the right fieldvalue

ontoit7331 — Tue, 23 Apr 2019 08:09:45 GMT

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?

Re: Sum IF fieldvalue=fieldvalue then subtract from the right fieldvalue

lironbaram — Wed, 24 Apr 2019 07:40:30 GMT

this expression works exactly as you want

i tested it in an app see below screen shot

also attach is a qlikview model