How to filter NULL in set analysis

[#1]

is there a way to filter a null value in set analysis?

Say I have the following values:

Family_Id	Amount
1	1368316
2	436479
5	18706
4	797
-	64367663

I want to display in one column the sum of families 1,2,5,4 (no problem with that) but I can't figure out a way to filter for the null family_id value (Family_Id-={1,2,4,5} is not working)

[#2]

never mind, I solved it with

sum

(Amount)-sum({<Family_Id={*}>} Amount)

[#3]

Your method is fine, but you can also use an empty element set and exclusion.

Sum({<Family_Id-={''}>} Amount

[#4]

Hi NMiller,

I gave this a try, but it's returning no values. I'm guessing this is checking for an empty not null value?

[#5]

The expression I gave should Sum non-null values of Family_Id. It is working when I test it on your data in an inline load (leaving the Null Family Id as a blank). The funny thing is, I always get 0 using your expression.

I attached the sample that I am using. I'm not sure why we're getting different results. Anyway, you got it working on your end and that's what counts.

[#6]

Sorry, double-post. I'm getting those server timeout errors again, so the attachment won't post (but the message does).

[#7]

I'm sorry, I should probably point out that I'm trying to filter a missing value, not a null value

[Anonymous]

The blank value in an inline load returns a blank string and not a proper null value. When loading an empty cell from Excel for example this will be treated as a proper null.

Of course you can do a second pass with a preceding load on an inline load and assign null() if the length is 0 in the field.

[#9]

Thanks for the info Johannes. I usually use Inline Loads when testing out my ideas before posting. I'll have to consider that in the future.