topic Re: chart expression filter for isnull() field values in QlikView

chart expression filter for isnull() field values

andries_bos — Sat, 16 Nov 2024 04:37:03 GMT

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.

Re: chart expression filter for isnull() field values

m_woolf — Wed, 30 Jan 2019 15:46:08 GMT

Maybe in script:

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

Then in your set analysis, you can use:

customer.Invoice.email={'NULL'}

Re: chart expression filter for isnull() field values

Anil_Babu_Samineni — Thu, 31 Jan 2019 00:54:17 GMT

May be

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

Re: chart expression filter for isnull() field values

andries_bos — Thu, 31 Jan 2019 09:23:20 GMT

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 ?