How to use Null() into a set analysis expression

[#1]

Hi , I have this set analysis expression but the values in 'HOTEL_AREA' are Null() so the expression is not filtering as expected.

How could I write correctly the set analysis expression to consider empty or Null() values instead of {''}?

-SUM({<HOTEL_AREA={''},TIPO_EMPRESA={'HOTEL'}>}REAL_PERIODO_USD)

Thank you.

[alexandros17]

You cannot manage directly null values in set analysis but you can use '*' to set all as you 've done, there is another option, if your HOTEL_AREA field is used as dimension you can exclude nul values by checking the box in the dimension page.

Let me know

[Gysbert_Wassenaar]

You can try using -={'*'}. A better solution is to replace the nulls in the script with real values.

[anbu1984 · Accepted solution]

SUM({<REAL_PERIODO_USD={'=Len(Trim(HOTEL_AREA))=0'},TIPO_EMPRESA={'HOTEL'}>}REAL_PERIODO_USD)

[MK_QSL]

Create one more field in the script just below the HOTEL_AREA

IF(IsNull(HOTEL_AREA) or LEN(TRIM(HOTEL_AREA))=0, 1, 0) as HOTEL_AREA_NULL_FLAG

Now use as below

=SUM({<TIPO_EMPRESA = {'HOTEL'}, HOTEL_AREA_NULL_FLAG  = {'1'}>}REAL_PERIODO_USD)

[#6]

I wish the easiest solution (HOTEL_AREA-={'*'}) had worked but it doesn't , I do not know why.

The solution "SUM({<REAL_PERIODO_USD={'=Len(Trim(HOTEL_AREA))=0'} " works ok, but I don't understand why, I do not understand its logic but is usefull in this case.

because I have other fields with null vaues, I wonder if there is a syntanxis in script to replace all the "len(trim('any_field))=0" by ''.

Something like:

Table:

load

if(len(trim(*))=0 , '' , * ) as *

from ...

[Gysbert_Wassenaar]

You replace nulls in fields in the script by adding these two lines at the start of the script:

NullAsValue *;

Set NullValue = 'NULL' ;

[#8]

if u are using "HOTEL_AREA" as dimension ...

just use "supress null values"

[anbu1984]

SUM({<REAL_PERIODO_USD={'=Len(Trim(HOTEL_AREA))=0'} --You will get correct result only when REAL_PERIODO_USD is unique across the rows.

Create ID using RowNo() in script and try this

=SUM({<ID={'=Len(Trim(HOTEL_AREA))=0'},TIPO_EMPRESA={'HOTEL'}>}REAL_PERIODO_USD)

Or

Create flag in script as below and use that in Set Analysis

Len(Trim(HOTEL_AREA)) As HOTEL_AREA_NULL_FLAG

=SUM({<HOTEL_AREA_NULL_FLAG={0},TIPO_EMPRESA={'HOTEL'}>}REAL_PERIODO_USD)