sum values where another value is null and ignore filters

[usr1]

Hi All,

I have a table that looks like this: 

ID	Record ID	Document Year	ID Amount
A	1	2022	$10
B	2	2022	$10
C	-	2022	$10
D	4	2022	$10

I would like to sum the ID Amount where the Record ID is equal to " - " (or null) and ignore the document year filter. 

I have tried using: 

=sum({<[Record ID]-= {'*'}, [Document Year]=,>}[ID Amount])

But that did not work. 

I have also read through the importance of nothing blog post  but did not find anything to lead me in the right direction. 

Any help you can provide would be great! Thanks! 

[vchuprina]

Hi,

Try the following expression, you will see a syntax error, but it works as expected

sum({-<[Record ID]-= {'*', '-'}, [Document Year]=>}[ID Amount])

In my example Record ID for ID C is - and for E and F are null

Regards,

Vitalii

[usr1]

Unfortunately that did not work. I should clarify the data comes from different tables. 

[vchuprina]

Hi,

It should work even if you data in different tables

sum({-<[Record ID]-= {'*', '-'}, [Document Year]=>}[ID Amount])

My data model:

Could you please try the following expression sum([ID Amount])? 

Does it show the sum of the ID Amount? If not, you should transform ID Amount to a number, because maybe qlik doesn't recognize your value as a number

Num(PurgeChar([ID Amount], '$')) AS [ID Amount]

Regards,

Vitalii