Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ptl14624
Contributor III
Contributor III

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
Creator II
Creator II

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

View solution in original post

7 Replies
Lisa_P
Employee
Employee

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

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.

vizmind.eu
ptl14624
Contributor III
Contributor III
Author

the minus sign should be before than the equal sign.

Tried anyway, doesn't work.

 

Thanks

rogerpegler
Creator II
Creator II

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
MVP
MVP

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
Contributor III
Contributor III
Author

Thanks a lot! It's working