Working with null values
Learn how null values work in Reports, and use different strategies to fix issues related to null values.
How it works
All aggregation functions ignore null values. This occurs because Reports cannot add a null value to a number. Null values display when a field in Projects, Results, or Strategy is not required.
Example calculations
- COUNT(1, 2 , 2 , 3 , null ,null) = 4
- COUNT DISTINCT (1, 2, 2, 3, null, null) = 3
- SUM (1, 2, 2, 3, null, null) = 8
- AVG (1, 2, 2, 3, null, null) = 2
- MIN (1, 2, 2, 3, null, null) = 1
- MAX (1, 2, 2, 3, null, null) = 3
How do I fix null values?
You can fix null values by doing any of the following:
- creating a calculated field
- creating a filter
- specifying a value for each record in the field
Creating a calculated field
You can fix null values by creating a calculated field that displays 0 if the value is null and apply an aggregation function on the calculated field:
Example
- Create the following calculated field:
- Calculated Field Name c_Days_To_Remediate
- Formula Type Simple
- SyntaxCASE WHEN Days To Remediate IS NULL THEN 0 ELSE Days To Remediate END
- Apply the appropriate aggregation function to the calculated field.
Result The aggregation is applied correctly.
Creating a filter or creating a calculated field
If you are reporting on timesheets data, you may need to either filter out time entries that belong to the organization, or create a calculated field to replace the Project Name value with the name of your organization.
Using the Timesheets feature in Projects, you can add time entries for administrative tasks that do not belong to any particular project. If these entries exist, the Project Name field contains null values.
Examples
Option 1: Create a filter
Create a Project Name filter to filter out any entry where Project Name Is Not Null.
By adding this filter, you are filtering out time entries that are associated with the organization, rather than with a particular project.
Option 2: Create a calculated field
Create the following calculated field:
- Calculated Field Name c_Time_Entries
- Formula Type Simple
- SyntaxCASE WHEN Project Name IS NULL THEN 'organization_name' ELSE Project Name END
Result The aggregation is applied correctly.
Specifying a value for each record in the field
Example
The table below shows null values in the Days to Remediate field.
If you applied a Sum aggregation on Days to Remediate, the value would be null.
Project Folder | Days to Remediate |
---|---|
Compliance | 100 |
Risk Management | |
SOX |
The table below does not have null values in the Days to Remediate field.
If you applied a Sum aggregation on Days to Remediate, the aggregation will apply correctly.
Project Folder | Days to Remediate |
---|---|
Compliance | 100 |
Risk Management | 40 |
SOX | 25 |