how to make conditional calculation

[vincenzo_montel]

Hi!
I have a list of accounts and an amount associated to them for every month, so something like this

Acccount Month Amount
acc1 gen 100
acc1 feb 100
acc1 mar 150
acc2 gen 200
acc2 feb -400

On another table there is a sign associated to every account

Account Sign
acc1 -
acc2 +

I need to build a measure for every account that is the result of the two steps

1) calculate the total amount of every account
2) check if the account's sign on the second table match the total sign, if not set the total to zero

So for the example shown above

Account NewTotal
acc1 0 (because the sign "-" on the second table doesnt't match the total's sign)
acc2 -200

Does exists a way to do it?
Thank you very much

PS: I use Qlik Sense 3.2

[Lisa_P]

For NewTotal expression:
If(Sign=-, 0, Sum(Amount))

[JordyWegman]

Hi Vincenzo,

Small addition to what @Lisa_P  said, this is the formula you need:

IF(Sign = '-',0,Sum(Amount))

While without the '-' single quotes, your formula will fail.

Also, probably a mistake in your post, but Account name needs to be the same while otherwise you don't have a link between the tables and the formula doesn't work.

Jordy

Climber 

[vincenzo_montel]

Thanks all for the answers, the if construct suggested works but the the application became more complex.....

Actually the tables involed are these (I have changed the table2 of accounts' signs and add a new column to the table1)

Table1

Category Acccount   Month   Amount
A                 acc1              gen           100
A                 acc1              feb            100
A                 acc1             mar            150
A                acc2              gen            200
A                acc2              feb            -400

Table 2

Account Sign
acc1           +
acc2           +

So for the example shown above

sumAcc1 = 350 and Account Sign "+" so with the if statement the actual sub total for acccont Acc1 is 350
sumAcc2 = -200 and Account Sign "+" so with the if statement the actual sub total for acccont Acc2 is 0

At the end what I need the app show is

CATEGORY TotalCAtegory
         A                      350