topic Set analysis exclude null in QlikView

Set analysis exclude null

Tue, 01 Nov 2011 17:59:06 GMT

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.

Set analysis exclude null

rrsrini2907 — Tue, 01 Nov 2011 18:08:07 GMT

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.

Set analysis exclude null

Wed, 02 Nov 2011 10:03:58 GMT

Shall I report this as a bug?

Set analysis exclude null

kaushiknsolanki — Wed, 02 Nov 2011 10:06:12 GMT

Hi,

try this expression.

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

Regards,

Kaushik Solanki

Set analysis exclude null

Wed, 02 Nov 2011 15:17:22 GMT

Hi Kaushik,

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

Set analysis exclude null

swuehl — Wed, 02 Nov 2011 15:28:12 GMT

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)

Set analysis exclude null

rrsrini2907 — Wed, 02 Nov 2011 16:37:25 GMT

It's kind of Bug.

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

You can just inform to Qlikview Support.

Regards,

Srini.

Re: Set analysis exclude null

IAMDV — Fri, 04 Nov 2011 14:57:27 GMT

Hi,

I would use somthing like this...

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

Cheers - DV

Re: Set analysis exclude null

danielrozental — Fri, 04 Nov 2011 18:11:08 GMT

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

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

Re: Set analysis exclude null

IAMDV — Sat, 05 Nov 2011 02:09:47 GMT

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

Thanks -DV

Re: Set analysis exclude null

Fri, 15 Jun 2012 08:04:04 GMT

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 )

Re: Set analysis exclude null

Wed, 20 Jun 2012 15:03:31 GMT

Hi Pawel,

Your solution works. We're using it to calculate the total sales amount for groups of stores, except some stores that don't have a storegroup assigned. I did it in this way.

Create a variable $StoresWithoutFormulaGroup with the expression:

=Concat( DISTINCT TOTAL If(IsNull(StoreGroupFormulaCode), Chr(39) & StoreCode & Chr(39)), ', ')

The result of this variable expression is something like: '100','101','102','103'. These are the storecodes without a storegroup.

We have different storegroups and stores can only be assigned to 1 of these groups. For example: 'Stores NL','Stores BE','Stores XL','Store in Store'.

The stores we want to exclude are stores in non of these groups.

Now create the following expression to summarize the sales per storegroup in a pivot table:

=Sum( {< StoreCode -= {$(StoresWithoutFormulaGroup) } > } SalesAmount )

And of course we can also select all storegroups to exclude the stores without a storegroup.

Re: Set analysis exclude null

Fri, 09 Oct 2015 10:55:34 GMT

You could also try: Sum({$<Group -= {'=(NULL())'} >} Amount)