Creating calculated fields

In the Reports app, calculated fields are fields that derive data from the calculation of other fields. You can use calculated fields to display the result of a calculation, substitute text values for numeric values, or evaluate one or more conditions and determine the value of a field based on the result.

How it works

Views provide you with fields from Diligent One that you can use to build a report. If you need to create additional fields as part of your report writing process, you can use some of the fields provided in the View as the basis for more complex calculations.

Simple formula builder

You can use the Simple formula builder to define calculated fields using any combination of:

  • dimension, metric, and date fields field type classifications

    For more information, see Creating reports.

  • logical operators operators, such as "between" or "not in list" that return a boolean result
  • aggregation functions tools that performs calculations or operations on a field, such as count or sum
  • mathematical equations operations, including addition, subtraction, multiplication, and division
  • CASE expressions conditional expressions used to change the way data is represented

Once you create a calculated field, the field is automatically added to a Calculated Fields folder in the data fields list, and you can add the field to your report.

Pre-Defined formula builder

As an alternative to defining your own calculated fields, you can use the Pre-Defined formula builder to the choose from a list of functions defined by Diligent.

The Pre-Defined formula builder is useful when you need to:

  • split delimited field values in order to report on sub-values
  • extract the month or year from a timestamp field
  • use advanced aging functions
  • convert character data types to numeric or date data types

    Tip

    Converting data types is helpful when reporting on custom attribute fields from Projects, which are typically stored as character data.

Note

Calculated fields created using the Simple formula builder can derive data from calculated fields using pre-defined functions. However, calculated fields using pre-defined functions cannot derive data from another pre-defined function.

Aggregation functions

All aggregation functions ignore null values. For more information about fixing null values, see Working with null values.

Aggregation functions and null values

Examples:

  • 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

Mathematical equations

Mathematical equations involving whole numbers and decimals always return precise results. Mathematical equations involving nulls or a division by 0 always return null.

Mathematical equations and results

Examples:

  • 8 + 2 = 10
  • 8 + 2.5 = 10.5
  • 8 + 2.5 + null = null
  • 8 / 0 = null

Examples

Example calculated fields

Result of a calculation

Calculated field name diff_risk_score

Syntax Objective Expected Risk Score - Objective Actual Risk Score

Result returns the difference of the Objective Expected Risk Score field and the Objective Actual Risk Score field

Substituted text values for numeric values

Calculated field name risk_assurance_rating

Syntax CASE WHEN Objective Risk Assurance <= 50 THEN 'Critical Risk Area: Follow Up Required' ELSE 'Unknown' END

Result returns 'Critical Risk Area: Follow Up Required' when Objective Risk Assurance value is less than or equal to 50

Evaluating multiple conditions and determining the value of a field based on the result

Calculated field name risk_assurance_rating

Syntax CASE WHEN Objective Risk Assurance <= 50 AND Issue Remediation Status = 'Open' THEN 'Critical Risk Area' ELSE 'Unknown' END

Result returns 'Critical Risk Area' when Objective Risk Assurance value is less than or equal to 50 and Issue Remediation Status is equal to 'Open'.

Permissions

Only Report Admins and Report Writers can create calculated fields.

Create a calculated field

  1. Open the Reports app.

    The Browse page opens.

  2. Open a report and navigate to the Data tab in Report Builder.
  3. At the bottom of the data fields list, click Create Calculated Field .

    The Calculated Field dialog box opens.

  4. Next to Calculated Field Name, specify a name for the calculated field.
  5. Next to Formula Type, select one of the following:
    • Simple allows you to build your own calculated fields
    • Pre-Defined allows you to select a function defined by Diligent
  6. Complete the following steps:
    If you selected Simple...If you selected Pre-Defined...
    1. Define an expression using the buttons at the bottom of the dialog box.
      Note

      Considerations:

      • Expressions containing values with apostrophes are not supported.
      • Aggregation functions must be added before the fields you want them applied to.
      • For any expression, you must click +Add after you add values and logical operators to your statement.
      • To edit a part of an expression, you must click Undo, and re-create it.
      • For boolean statements, enter 0 to represent 'No', and 1 to represent 'Yes'.
    2. To test the expression, click Validate.

      A message displays to confirm that the expression is valid or invalid. If the expression is invalid, you are prevented from saving the calculated field, and you must update the syntax before proceeding.

    3. Click Save.

      Result A Calculated Fields folder is automatically created in the data fields list, and the newly created calculated field is added to the folder.

    1. Select a function from the list.

      For a complete list of functions, see Pre-defined functions.

    2. Select the field type of the calculated field you want to create.
    3. Define the fields to use in the function.
    4. Click Save.

      Result A Calculated Fields folder is automatically created in the data fields list, and the newly created calculated field is added to the folder.

Add a calculated field to a report

  1. Open the Calculated Fields folder in the data fields list.
  2. Drag the calculated field to the appropriate area in the report.

    Result The calculated field is added to the report.

Pre-defined functions

Description

Returns the number of elapsed days (the age) of a specified date.

Syntax 

AGE(Date: timestamp)

Parameters

Name Type Description
Date Timestamp The field to age.

Output

Number.

Example

Returns the number of days between the issue remediation deadline date and the current date:

AGE(Date: Issue Remediation Deadline Date)
  • If a positive value is returned, it is equal to the number of days in the past when the issue remediation deadline date occurred.
  • If a negative value is returned, it is equal to the number of days in the future when the issue remediation deadline date will occur.
  • If 0 is returned, the issue remediation deadline date is the current date.

Description

Returns the number of elapsed days between two dates.

Syntax 

DATEDIFF(Start Date: timestamp, End Date: timestamp)

Parameters

Name Type Description
Start Date Timestamp The field to age.
End Date Timestamp The field to which Start Date is compared.

Output

Number.

Example

Returns the number of days between the issue identified date and the issue actual remediation date:

DATEDIFF(Start Date: Issue Identified Date, End Date: Issue Actual Remediation Date)
  • If a positive value is returned, the issue identified date is before the issue actual remediation date.
  • If a negative value is returned, the issue actual remediation date is before the issue identified date.
  • If 0 is returned, the issue identified date and the issue actual remediation date are the same date.

Description

Extracts the month from a specified timestamp and returns it as a numeric value (1 to 12).

Syntax 

MONTH(Date:timestamp)

Parameters

Name Type Description
Date Timestamp The field to extract the month from.

Output

Integer (1-12).

Example

Returns the month (1-12) for each value in the Issue Actual Remediation Date field:

MONTH(Date: Issue Actual Remediation Date)

Description

Extracts the year from a specified timestamp and returns it as a numeric value using the YYYY format

Syntax 

YEAR(Date: timestamp)

Parameters

Name Type Description
Date Timestamp The field to extract the year from.

Output

Integer.

Example

Returns the year (YYYY) for each value in the Issue Actual Remediation Date field:

YEAR(Date: Issue Actual Remediation Date)

Description

Returns the number of elapsed days (the age) of a specified date.

Syntax 

AGE_C(Date: text)

Parameters

Name Type Description
Date Text The field to age.

Output

Number.

Example

You have a custom field defined for issues that specifies a date value. You want to perform an aging calculation on the field, however, custom fields are represented as text values in the Reports app. You use the Text - Aging function to perform the aging calculation.

Returns the number of days between the custom field date and the current date:

AGE_C(Date: Custom Issue Attribute 1)
  • If a positive value is returned, it is equal to the number of days in the past when the custom field date occurred.
  • If a negative value is returned, it is equal to the number of days in the future when the custom field date will occur.
  • If 0 is returned, the custom field date is the current date.

Description

Converts a date text field to a timestamp.

Syntax 

CHAR_TO_DATE(Date: text)

Parameters

Name Type Description
Date Text The field to convert to a timestamp.

Output

Timestamp.

Example

You have a custom field defined for issues that specifies a date value. You want to perform calculations on the field, however, custom fields are represented as text values in the Reports app. You use the Text - Convert to Date function to convert the field to a timestamp:

CHAR_TO_DATE(Date: Custom Issue Attribute 1)

Tip

The output is a timestamp, which displays both the date and time. If you only want to display the date, you can format the display of the field and specify it as a Date. For more information, see Formatting columns in reports.

Description

Converts a number text field to a numeric value.

Syntax 

CHAR_TO_NUM(Text: text)

Parameters

Name Type Description
Text Text The field to convert to a numeric value.

Output

Number.

Example

You have a custom field defined for issues that specifies a numeric value. You want to perform calculations on the field, however, custom fields are represented as text values in the Reports app. You use the Text - Convert to Numeric function to convert the field to a numeric value:

CHAR_TO_NUM(Text: Custom Issue Attribute 1)

Description

Returns the number of elapsed days between two dates.

Syntax 

DATEDIFF_C(Start Date: text, End Date: text)

Parameters

Name Type Description
Start Date Text The field to age.
End Date Text The field to which Start Date is compared.

Output

Number.

Example

You have two custom fields defined for issues that specify numeric values. You want to calculate the number of days that have elapsed between the two dates, however, custom fields are represented as text values in the Reports app. You use the Text - Days Between Dates function to calculate the number of days.

Returns the number of days between the first custom field date and second custom field date:

DATEDIFF_C(Start Date: Custom Issue Attribute 1, End Date: Custom Issue Attribute 2)
  • If a positive value is returned, the first custom field date is before the second custom field date.
  • If a negative value is returned, the second custom field date is before the first custom field date.
  • If 0 is returned, the two custom field dates are the same date.

Description

Extracts the month from a specified text field and returns it as a numeric value (1 to 12).

Syntax 

MONTH_C(Date: text)

Parameters

Name Type Description
Date Text The field to extract the month from.

Output

Integer (1-12).

Example

You have a custom field defined for issues that specifies a date value. You want to extract the month from the field, however, custom fields are represented as text values in the Reports app. You use the Text - Extract Month function to extract the month from the field.

Returns the month (1-12) for each value in the custom field:

MONTH_C(Date: Custom Issue Attribute 1)

Description

Extracts the year from a specified text field and returns it as a numeric value using the YYYY format.

Syntax 

YEAR_C(Date: text)

Parameters

Name Type Description
Date Text The field to extract the year from.

Output

Integer.

Example

You have a custom field defined for issues that specifies a date value. You want to extract the year from the field, however, custom fields are represented as text values in the Reports app. You use the Text - Extract Year function to extract the year from the field.

Returns the year for each value in the custom field:

YEAR_C(Date: Custom Issue Attribute 1)

Description

Extracts a particular segment of data from a field. The segment must appear in the same position in each field.

Syntax 

SPLIT(Text: text, Separator: text, Segment: integer )

Parameters

Name Type Description
Text Text The value to extract the segment from.
Separator Text

The character or characters that delimit, or indicate, segments of data in a source string.

You can also specify a regular expression as a separator. See the Multi-character and space delimiters examples below.
Segment Integer

The segment to extract.

Use a number to specify which segment to extract. For example, to extract the third segment, specify 3.

Output

Character.

Examples

The Text - Split function breaks character data into segments based on separators such as commas or spaces and returns a specified segment:

Comma-delimited segments Multi-character and space delimiters

Returns "North America":

SPLIT("North America, Canada, British Columbia", ",", 1)

Returns "British Colombia"

SPLIT("North America, Canada, British Columbia", ",", 3)

Returns "" (the third segment is empty):

SPLIT("North America, Canada, ,British Columbia", ",", 3)

Returns "A":

SPLIT("ABC", "/*", 1)

Returns "Doe":

SPLIT("Jane Doe", "\s+ ", 2)