Writing expressions - Counts and nulls

[jbillman]

First - is there a good place to learn the qlik expression language that anyone particularly enjoys? I'm trying to use ChatGPT and not finding it terribly helpful.

My main question is out of this dataset that I have attached, the request is to calculate the:

Count of Consent Date, where Consent Date is not Blank, the Inactive field does not equal YES, and the Delivery Date IS blank.

How could I structure this? I've tried multiple ways and keep getting 0's. It looks like any blanks are being populated with "-" when I look in Data Manager, and I've tried to update formulas accordingly but still not having success.

[BrunPierre]

=Count(Disitnct{<[Consent Date]={"*"},Inactive-={"YES"},[Delivery Date]={"=Len(Trim([Delivery Date]))=0"}>} [Consent Date])

For learning about Qlik's expression language, you could benefit from exploring the Qlik Help documentation and participating in discussions on the Qlik Community forums. Additionally, various online courses and tutorials on platforms such as Udemy, Coursera, and YouTube can be valuable resources. If you encounter difficulties understanding any particular topic, don't hesitate to request clarification.

[marcus_sommer]

A very good source for set analysis is:

Set Analysis: syntaxes, examples - Qlik Community - 1491810

but you need also to understand what's NULL and how it could be handled:

NULL handling in QlikView - Qlik Community - 1484472

[jbillman]

@BrunPierre @marcus_sommer thank you very much for your responses! I will definitely check out those resources to up my game in Qlik (I'll need it as our business is going all in).

In regards to my query/expression, unfortunately it's still pulling in 0 as the count. When I attempt to just manually filter the data in excel to get the count, I believe my answer should be 26. I'm just not sure what I'm doing wrong either in the data formatting or the expression itself.

[marcus_sommer]

Very important to understand is that (real) NULL doesn't exists - it means NOTHING and couldn't be accessed, displayed or counted. The above provided link explained the various kinds of NULL and possible ways to handle very beautifully.

Further be aware that NULL isn't mandatory an error and there are also benefits to keep them as they are. Nevertheless as a quick & dirty approach just load your few fields with something like:

if(len(trim(MyField)), MyField, '<NULL>') as MyField

With doing it you could select everything directly and also defining it within the set analysis and an adaption of the suggestion from @BrunPierre may look like:

=Count(Distinct{<[Consent Date]={"*"},Inactive-={'YES'},[Delivery Date]={'<NULL>'}>} [Consent Date])