Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Master II
Master II

Maybe in script:

NullAsValuecustomer.Invoice.email;
Set NullValue = 'NULL';

Then in your set analysis, you can use:

customer.Invoice.email={'NULL'}

Highlighted

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)
Highlighted
Creator
Creator

Using 

= 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 ?