topic Re: Counting Null Values in a data set based on two variables. in QlikView

Counting Null Values in a data set based on two variables.

leale1997 — Mon, 30 Mar 2015 19:21:11 GMT

I have read variations of what I am trying to do but have not been able to acclimate any solution for my specific needs. Pulling my hair out because it is actually very simple. I have a data set that has two fields "Record Id" and "Date Closed". I want to code a text object to view a one number answer that will represent how many [Date Closed] fields contain Null Values. In order to get the count correct I need the formula to count based on Distinct [Record Id].

Here are a couple of the variations I have tried:

=NullCount({<[date_closed]>} Distinct [Record ID]) (Didn't work looks for null values in both fields)

=NullCount({<[date_closed]>} and Distinct [Record ID]) (Didn't work returns no value)

Re: Counting Null Values in a data set based on two variables.

maxgro — Mon, 30 Mar 2015 19:42:11 GMT

count({$ -<[Date closed]={'*'}>} [Record ID])

Re: Counting Null Values in a data set based on two variables.

leale1997 — Mon, 30 Mar 2015 19:54:22 GMT

For some reason that is still not working. I plugged it in and it is returning a 0 count.

Could be because I am a novice and not understanding the formula. Or not explaining my data set well.

The data set looks like this:

Date_Closed Record_ID

1/01/2015 1

2/15/2015 5

2/15/2015 7

null 10

null 15

The initial formula I used was = 'Open' & NullCount ([date_closed])

Instead of '2' it returned like 2000. Counting duplicates somewhere.

So I am trying to cut out the duplicate count by using the unique Record_ID.

Re: Counting Null Values in a data set based on two variables.

maxgro — Mon, 30 Mar 2015 20:06:30 GMT

I tried with (if(Date_Close....... to make true null)

load if(Date_Closed='null',null(),Date_Closed) as Date_Closed, Record_ID inline [

Date_Closed , Record_ID

1/01/2015 , 1

2/15/2015 , 5

2/15/2015 , 7

null , 10

null , 15

];

and expression is

count({$ - <[Date_Closed]={'*'}>} distinct [Record_ID]) result is 2

count({$ - <[Date_Closed]={'*'}>} [Record_ID]) result is 3

Re: Counting Null Values in a data set based on two variables.

leale1997 — Mon, 30 Mar 2015 20:19:40 GMT

Thanks so much. After I read your suggestion to use the Load statement I realized the problem was in the data load. Duplicate field names in other tables were causing the count to be off. Always the easy obvious thing that gets over looked.

Re: Counting Null Values in a data set based on two variables.

leale1997 — Mon, 30 Mar 2015 21:30:38 GMT

Can i throw one more at ya?

Same data trying to count records that have null values in the [Date_Closed] field and a [due_date] that is less than today()

I tried to use one of the examples you provided be before. Just adding in the extra variable.

=count({$<[date_closed]-={"NULL"}>} [Due Date]< today())