Solved: Set analysis exclude null - Qlik Community

Report Inappropriate Content · ‎2011-11-01

I'm trying to create a set analysis and exclude null records on a field. Something like this:

Sum({$<Group -= Null()>} Amount)

I found something saying to do it like the following line, but the editor still gives me a curly red underlining.

Sum({$<Group={*}>} Amount)

Could someone give me the right way to do this?

I'm working with QV version 11 RC.

Report Inappropriate Content · ‎2012-06-15

I think I know the right syntax.

If you want to choose only NULL values you should use:

Sum( {$ <Group = {$(=list_of_groups_with_NULL)}> } Amount )

where list_of_groups_with_NULL is generated like this:

Concat( DISTINCT TOTAL If(IsNull(Group), Chr(39) & Group & Chr(39)), ', ')

Now you can just use this statement with "-" to choose all groups without NULLs:

Sum( {$ <Group - = {$(=list_of_groups_with_NULL)}> } Amount )

rrsrini2907 · ‎2011-11-01

No, Some time it used to say the expression is error but still it work fine.

Just go ahead and check the result for the expressions.

Regards,

Srini.

Report Inappropriate Content · ‎2011-11-02

Shall I report this as a bug?

kaushiknsolanki · ‎2011-11-02

Hi,

try this expression.

Sum({$<Group -= {""}>}Amount)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

Report Inappropriate Content · ‎2011-11-02

Hi Kaushik,

QV still gives a syntax error for your expression. As Srini mentioned the code is working, but the syntax error is annoying.

swuehl · ‎2011-11-02

I think this is a known bug of the syntax checker. If you don't like the misleading false syntax error, use something like

Sum({$<Group = Group - {Value}>}Amount)

rrsrini2907 · ‎2011-11-02

It's kind of Bug.

Since they didn't mention that as a correct syntax.

You can just inform to Qlikview Support.

Regards,

Srini.

IAMDV · ‎2011-11-04

Hi,

I would use somthing like this...

Sum({$<Group -= "=Null()">} Amount)

Cheers - DV

danielrozental · ‎2011-11-04

There's no way to select nulls or unselect nulls, this is what you should do, select everything else

Sum({$<Group = {'*'}>} Amount)

IAMDV · ‎2011-11-04

Daniel - Not sure...but my expression works and it excludes nulls. I have used the above expression many times with QV SR7.

Thanks -DV

Set analysis exclude null