Counting NULLS in Chart Script not working

[rmadursk]

I have a set of data for tracking training/certifications. I want to count and provide a percentage of trained individuals that have/have not attained a certification.  I have a table with names, training sessions, and certifications.  If no certifications have been attained the field is Null. Here is a piece of the table:

Obviously, no names here. But when I try to count those nulls I get 0.

[Designations/Certifications/Licenses] is the expression for the column labelled the same in the table above.

This is my latest attempt at an Expression:

Nullcount([Designations/Certifications/Licenses])

Should be 7 but I get:

Before I found the NullCount function I had used:

count(IsNull([Designations/Certifications/Licenses])

Which gives me:

That is how many certifications were attained, not how many Nulls are there. I also had an if() statement in there at one point but decided it wasn't necessary. It also returned 23 in this case.

What am I missing/doing wrong?

[rwunderlich]

Can you post a screenshot of your data model from the data model viewer? If the Certifications are in a linked table you will have to take a different approach. 

BTW, the problem with your Count() approach if that Count counts anything. IsNull() returns 0 or -1 and both would be counted. If you wanted to "count" only where IsNull returns true (-1) the correct expression would be:  -Sum(IsNull(something)) -- however won't solve your problem here.

Post the data model and we'll make some suggestions. 

-Rob

[Graceleah4181]

Good question.

[rmadursk]

Thanks for the tip on counting Nulls.  I don't remember seeing that little tidbit in the documentation anywhere, but it might be in a place I haven't been yet.

Here is the model.  Name comes from Employee Roster. Session comes from WorkForce_Development/training 20240617. Designations/Certifications/Licenses comes from Certifications.  So, I think Rob has hit on the problem. 

That leads me to believe that if I pull the Name from the Certifications table instead of the Employee_Roster table it might work, or something like that strategy? Was also thinking that maybe I need another table created during my data load to use for this (and other) results to work.

[Or]

Perhaps you could try:

Sum(RangeMin(Len([Designations/Certifications/Licenses]),1))

[rmadursk]

This one (Sum(RangeMin(Len([Designations/Certifications/Licenses]),1))) did not produce the desired result for this requirement, but I think I can use it in another place in the App.  Thanks.

[rwunderlich]

How about

Count({<Email={"Count([Designations/Certifications/Licenses])=0"}>}NAME)

or

Count({<Email=E(<[Designations/Certifications/Licenses]={"*"}>)}NAME)

-Rob

[rmadursk]

It seems like those should work, but they don't 🙂 

In the first one I think it is creating a set of Email addresses that do not have a Certification and then counting the NAME associated with the Email.  This seems like it should work but it gives me 0 no matter what selections I make in the chart, or if I change the 0 to any other value in the expression.

In the second one I get an error about "error in set modifier element function". Haven't figured out why that's the case, but here's my interpretation of the expression. I think it is creating a set of Email addresses that are being excluded if they have a Certification and then counting the NAME associated with the Email that were "included" or remaining (ie. Did not have a cert). 

I've not used a set modifier in the past so that's a learning experience. I've played around with the syntax some and can't seem to find the right combination, yet.  Based on the Qlik docs it looks just fine.

Looking at the solutions provided, I see where we are trying to go with this and feel like we're getting closer.   

[lennart_mo · Accepted solution]

Hi @rmadursk,

if i remember correctly the syntax for the second function should be something like this:

Count({<Email=E({<[Designations/Certifications/Licenses]={"*"}>})>}NAME)

It's easy to lose track of all the brackets when using these 😄

I hope this resolves your issue!

[rmadursk]

Many thanks to Lennart and Rob for help with this.  Learned some new stuff and will not be frustrated thinking about it all weekend.

Ron