Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Read about the latest Qlik Community enhancements on the Community News blog!
Showing results for 
Search instead for 
Did you mean: 

chart expression filter for isnull() field values

I use a program to send invoices by mail. It uses some emaill adresses stored in the field customer.Invoice.email . 

In a list of customers I would like to count the total amount of invoices but also count the amount of invoices has been send by mail and the count that has been send by postal services (because no mail address available). 

My working default invoice total counter for the current year is : 

= count({< Jaar={$(=Max(Year))}, Schema = {'Omzet'}   > } DISTINCT(DocumentNr))  

This works for counting all invoices, but I would also like to count the amount of invoices that could be processed via the email address and the 'rest' that has to be printed.

These counters should be filtering on the field  customer.Invoice.email . Is this email value absent this would be a null value. 

This filter in my opinion would be to check : isnull(customer.Invoice.email) . 

Adding this extra filter to the expression this would therefor be  :

= count({< Jaar={$(=Max(Year))}, Schema = {'Omzet'} , isnull(customer.Invoice.email)  > } DISTINCT(DocumentNr))  

but this last added filter leads to an expression syntax error.

How could I add this extra filter to the expression to create a counter for isnull(customer.Invoice.email) and a second counter not isnull(customer.Invoice.email)   invoices. 









Labels (4)
3 Replies
Master II
Master II

Maybe in script:

Set NullValue = 'NULL';

Then in your set analysis, you can use:



May be

= count({< Jaar={$(=Max(Year))}, Schema = {'Omzet'} , customer.Invoice.email = {"=Len(customer.Invoice.email)=0"}  > } DISTINCT(DocumentNr))  

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)


= count({< Jaar={$(=Max(Year))}, Schema = {'Omzet'} , customer.Invoice.email = {"=Len(customer.Invoice.email)<>0"} > } DISTINCT(DocumentNr)) to count all invoices where the email is available does work. 

however almost the same expression 

= count({< Jaar={$(=Max(Year))}, Schema = {'Omzet'} , customer.Invoice.email = {"=Len(customer.Invoice.email)=0"} > } DISTINCT(DocumentNr))  does not work ?


I can subtract the amount sent by mail from the  total counter to get my second value , but I would expect there should be an easier solution ?