Consider Null Values in a QlikSense Formula

[ptl14624]

Hi.

I've set 2 Expressions: 'Faturação 1' and 'Faturação 2'.

The only difference between them is the condition "Área de Produto do Prod Serviço", that as you can see, is empty is this example.

So, in 'Faturação 2' I want to catch all 'Área de Produto do Prod Serviço' that is different than IM and IR (so, I want to get null values as well).

What condition should I use?

Thanks

[Lisa_P]

try the minus symbol after the = sign
[Area de Produto do Prod Servico]=-{'IM','IR'}

[Ivan_Bozov]

Hi! It could be a good idea to assign a value to the missing fields. You can do this in the script:

IF(LEN(TRIM([Área de Produto do Prod Serviço]))=0, 'Missing', [Área de Produto do Prod Serviço]) AS [Área de Produto do Prod Serviço]

and then your formula should work the way you have it.

[ptl14624]

the minus sign should be before than the equal sign.

Tried anyway, doesn't work.

Thanks

[rogerpegler · Accepted solution]

Having [Área de Produto do Prod Serviço] -= {'IM','IR'} in the set analysis is saying choose those records associated with [Área de Produto do Prod Serviço] but not those with values of 'IM' or 'IR'. This will miss the records not associated with [Área de Produto do Prod Serviço]. 

Try

Sum({<[Gen_Prod_Posting Group]= {'SP-PARTS','CONSUMABLE','SERVICOS'},[Referencia]-={'RENT'}, [G_L Account]= {"7*"}> - < [Área de Produto do Prod Serviço] = {'IM','IR'}>}Valor)

Note:

I don't have your character set available so fields like Referencia are wrong and copy/paste won't work!

The GL Account set should be in double quotes if you want to match all accounts beginning with 7. Using single quotes will attempt to match the actual text - this is a change of behavior as described :

https://community.qlik.com/t5/Qlik-Design-Blog/Quotes-in-Set-Analysis/ba-p/1471824

[rwunderlich]

@rogerpegler is on the right track. You cannot select nulls so you must select the set 'IM','IR' and use the complement.  See this post https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704 for an explanation and alternative using E().  Note the post also explains the difference between -= and =-, both of which have uses. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Vegar]

I agree with @rwunderlch that E() is a good way to go. An alternative solution is to treat your field NULL values as a value and thereby make it selectable.

Example:
NullAsValue [Area de Produto do Prod Servico];
Set NullValue = 'NULL';
LOAD ...

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegular...

[ptl14624]

Thanks a lot! It's working