How to Exclude Null Value from Count

[#1]

This is probably something easy, but I cannot figure it out. I have an app with several count functions - where I am counting the whole  universe of distinct Agency IDs in order to eventually calculate penetration. However, some records lack an 'Agency Type' field value and so it is null. So, when I count the 'Types' it is showing Null as an option with a count of 1. Here's what I get:

Formula for the Count Universe expression is:

Count(DISTINCT{1}[AGENCY_ID])

Even though some records do not have an Agency Type (i.e. it is Null), I don't want to count them. I want to count only ALL Distinct Agency IDs in the data universe as long as they're not Null. Any ideas?

[rwunderlich · Accepted solution]

Select the "Exclude Null Values" checkbox on the DImensions tab,

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#3]

Oh, duh! Works like a charm. There are so many little nuances, it's hard to keep it all straight. Thanks, Rob!