Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ptl14624
New Contributor II

Consider Null Values in a QlikSense Formula

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

 

1.JPG2.JPG

Labels (1)
1 Solution

Accepted Solutions
rogerpegler
Contributor

Re: Consider Null Values in a QlikSense Formula

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

7 Replies
Employee
Employee

Re: Consider Null Values in a QlikSense Formula

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

Re: Consider Null Values in a QlikSense Formula

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
New Contributor II

Re: Consider Null Values in a QlikSense Formula

the minus sign should be before than the equal sign.

Tried anyway, doesn't work.

 

Thanks

rogerpegler
Contributor

Re: Consider Null Values in a QlikSense Formula

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

MVP & Luminary
MVP & Luminary

Re: Consider Null Values in a QlikSense Formula

@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

Partner
Partner

Re: Consider Null Values in a QlikSense Formula

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...
???
Celebrating my 300 accepted solution in the Qlik Community
???
ptl14624
New Contributor II

Re: Consider Null Values in a QlikSense Formula

Thanks a lot! It's working