Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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