3 Replies Latest reply on Dec 30, 2014 3:19 PM by Theresa Dzieciatkowski

# How can I create a calculated field that will count rows where a date is missing?

Hi, I am trying to build a report that has the following rows:

Date Created: Date the issue was created

Date Completed: Date the issue was resolved

IssueID: Distinct ID to identify the issue

I want to find the number of open issues. I thought I would count the distinct IDs where Date Completed was null but any equation I use ends up in an error.

Can somebody point me in the right direction? This is the equation I have been trying to use:

CASE IFNULL([Date Completed],00/00/0000 )  WHEN 0 THEN COUNTD([Issue ID] ) END

Thank you, Theresa

• ###### 1. Re: How can I create a calculated field that will count rows where a date is missing?

There are a few ways to accomplish this, depending on how you want it to be presented. Attaching a sample workbook would help find the way the fits the best for your situation.

The simplest way to get a count of records with a null date is to drag the date field to the Filters shelf, click Next, then Special, and select "Null dates." Then drag Number of Records to the Text shelf. Done.

Option 2: just put ISNULL([Date Completed]) in a calculated field called "Date Completed is NULL," and drag that to the rows or columns shelf. Then drag Number of Records to the text shelf.

Option 3: use the calculated field above in another calculated field containing:  countd(if [Date Completed is NULL] then [Issue ID] end)

There are probably many other variations...

I hope that helps.

bl

• ###### 2. Re: How can I create a calculated field that will count rows where a date is missing?

I thought I would count the distinct IDs where Date Completed was null but any equation I use ends up in an error.

As Bill mentioned, there are many ways to calculate this. Here is one that uses aggregates:

SUM([Number of Records]) - COUNT([Product Base Margin])

I want to find the number of open issues.

This is one of the most frequently asked questions. See most of them here:  FAQ: Open & Close Dates

I know technique #1 and #2 in  The Cross Join Collection  work well with open and close dates.

The minus is that they are based on custom SQL which might be too technical and perform poorly.

• ###### 3. Re: How can I create a calculated field that will count rows where a date is missing?

Thank you All!

I ended up doing this:

I created a Calculated Field Called OPEN ISSUES and used this equation: countd (if [Date Completed is NULL] then [Issue ID] end)

Then to get the % of issues closed, I created another calculated field and used this equation: ([NUM_ISSUES]-[OPEN ISSUES])/[NUM_ISSUES]