Subtract from sum

[alenb]

Consider the following data:

Date, Units, Return
2020-10-01, 1,
2020-10-01, 1, 
2020-10-01, 1, X
2020-10-02, 1,

I need to sum the Units but subtract where the Return is marked with X. So, final output should be 2.

This is what I'm thinking of:

sum(${<Return-={X}>} Units) - sum(${<Return={X}>} Units)

So, first sum all where we have no X, then subtract the ones where we have the X. 

This works but is there a more 'elegant' solution? This expression is used in many other expressions and ideally I wouldn't be repeating it all over the place.

[sergio0592]

Hi,

Try with

 =sum({$<Return-={"X"}>} Units)

And take a look here

https://community.qlik.com/t5/Qlik-Design-Blog/Implicit-Set-Operators/ba-p/1475624 

[alenb]

Sorry, this won't work, I had to correct my question with your input. It is not to exclude the Return=X, but to actually mathematically subtract it from the others that don't have X. 

I only realized this once I tried your approach on my test data. 

So it should sum all where Return -=X and then subtract where Return=X