3 Replies Latest reply: May 11, 2017 6:29 AM by Marcus Sommer

# IsNull in set analysis with variable

Hi guys, I hope you can help me out with this one:

I want to make a set analysis with a variable where Dim2 is null, but my code doesn't give me the correct results:

sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, [Dim2] = {''} >}  aggr(\$(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))

• ###### Re: IsNull in set analysis with variable

check whether this is working?

sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}>}  aggr(\$(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))

It it is working for you, It should work

sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, IsNull([Dim2]) = {0} >}  aggr(\$(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))

• ###### Re: IsNull in set analysis with variable

This code works, but it doesn't give me all the variables I need:

v_New = ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, [Dim2] = {''} >}  aggr(\$(v_1), [Dim1], [Dim2])) / 1000

In the expression:

sum(aggr(\$(v_New), [Dim1], [Dim3]))

When I change my variable code to yours, it gives me a red line beneath the {0} and no values

• ###### Re: IsNull in set analysis with variable

AFAIK this [Dim2] = {''} won't query NULL else if the field-value is empty which could be considered as a kind of NULL but it's not the same and not NULL for qlikview. Instead of this you could try something like this: [Dim2] -= {'*'}. Another approach could be to replace NULL with "real" values maybe with something like:

if(len(trim([Dim2])) = 0, '#NV', [Dim2]) as [Dim2]

More could you find here: NULL handling in QlikView.

- Marcus