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

[leale1997]

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)

[maxgro]

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

[leale1997]

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.

[maxgro · Accepted solution]

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

c:

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

null        ,            15

];

and expression is

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

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

[leale1997]

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.

[leale1997]

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())