Formula building in a calculated metric definition
Summarize
Summary of Formula building in a calculated metric definition
In ServiceNow, a calculated metric definition enables you to build formulas using operands, operators, and functions to perform calculations on metric data. This capability allows you to derive new metrics by combining existing metric definitions, such as calculating total employee count from male and female employee metrics.
Show less
Key Features
- Operands and Operators: Formulas consist of metric definitions as operands and mathematical operators (e.g., +, -, , /) or functions to compute results.
- Default Operand Values: You can configure default values for operands in the Calculated Metric Definition Settings table. When an operand lacks data, the system uses the default value to complete the calculation, ensuring formula robustness.
- Formula Versions: Editing and saving a formula after execution creates a new version, tracked with applicable date ranges. This versioning facilitates historical tracking and updates to calculation logic over time.
- Calculation Levels: You must specify the calculation level before saving a formula:
- Metric Definition Level: Aggregates data across all child metrics; the formula is executed to generate calculated metric data.
- Entity Level: Creates child metrics per distinct entity involved in the operands, supporting more granular calculations; after execution, metrics can be aggregated to produce the calculated metric.
- Ad Hoc Data Limitation: Only scheduled child metric data is included in formula calculations. Ad hoc metric data is excluded.
- Formula Builder Configuration: Before building formulas, specify the formula context, tables, and identifiers to accurately reference metric data.
- Formula Import: You can import formulas directly from Microsoft Excel spreadsheets to accelerate formula creation.
- Editability: Formulas in calculated metric definitions can be edited anytime to refine calculation logic or adjust for historical data changes.
Practical Benefits for ServiceNow Customers
This functionality empowers you to create custom metrics tailored to your organizational needs by combining and manipulating existing metric data. The ability to define default values, manage formula versions, and select calculation levels enhances control and accuracy. Importing formulas from Excel streamlines adoption, while formula editing supports ongoing refinement. Understanding the exclusion of ad hoc data ensures clarity in metric calculations.
In a calculated metric definition, you can build formulas using operands, operators, and functions to perform calculations on metric data.
A formula consists of operands, operators, and functions. For example, to calculate the total employee count from two metric definitions (number of male employees and number of female employees), the metric definitions are the operands, and the operator is the symbol or function that performs a specific operation on the operands to produce a result. Examples of operators include addition (+), subtraction (-), multiplication (*), and division (/).
Default values for formula operands
You can set default values for operands in the Calculated Metric Definition Settings table. When a formula encounters an operand with no value, the system applies the configured default value from this table and completes the calculation. You can activate the shipped default record or create a custom entry with preferred values for specific operands.
Formula versions
Each time you save an edited formula on a calculated metric definition that has been executed, a new formula version is created. Formula versions are listed in the Versions related list on the calculated metric definition. Each version has an Applicable from date and an Applicable to date. The Applicable to date is empty for the currently active version and is set to the day before the new version's Applicable from date when a newer version is saved.
You can edit a formula at any time, including after the calculated metric definition has been executed. For more information, see Edit a calculated metric definition formula.
Formula calculation levels
- Metric definition: If you select Metric definition in the Calculation level field, the data across all child metric definitions or child metrics is used for calculation. When you select Execute, the formula is applied and the calculated metric definition data is generated. For more information, see Configure the formula builder.
- Entity: If you select Entity in the Calculation level field and specify the calculation method using the formula builder, child metrics are created for the calculated metric definition. These metrics are created for each distinct entity associated with the metric definitions used as operands in the formula. When you select Execute, the formula is applied and the metric data is generated. When you select Aggregate, the metric data is aggregated and the calculated metric data is generated.
Ad hoc metric data from child metrics is not included in CMD formula calculations at either level. Only data from scheduled child metric tasks feeds into the CMD score. For more information, see Ad hoc metric data task limitations.