Set analysis exclude null

[#1]

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.

[#2 · Accepted solution]

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  )

[#3]

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.

[#4]

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