topic How to Exclude Null Value from Count in QlikView

How to Exclude Null Value from Count

Thu, 21 Apr 2016 21:20:27 GMT

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?

Re: How to Exclude Null Value from Count

rwunderlich — Thu, 21 Apr 2016 21:26:18 GMT

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: How to Exclude Null Value from Count

Thu, 21 Apr 2016 21:33:41 GMT

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