Metric Formulas
You can develop Acumen Fuse metrics using a basic or advanced approach, or a combination of the two.
- Basic is a filter-based set of metrics that doesn't require detailed formula definition.
- Advanced uses detailed formulas to define a metric beyond a simple filter.
Formulas use array-based formulas in their calculations. These are explained in detail below. The formulas enable you to group and aggregate multiple activities together so that results for a ribbon or phase or intersection can be calculated.
Types of Fields
When creating metric formulas, you can reference four types of fields:
| Field Type | Description |
|---|---|
| Activity Fields | Activity fields are the most commonly used type of field in an Acumen Fuse metric formula. All fields that are defined in the field mapping during a project import are exposed as activity fields in the metric editor. |
| Project Fields | Some project level fields get automatically imported during a project import. These fields are also exposed for use within metric formulas. When a metric is calculated that contains a project field reference, the specific project field value for the activity in question is used.
A single metric calculation may contain activities from multiple projects. In this instance, the appropriate project level field value will be used for each activity (for example, Time Now may be different for each of the projects). Project fields include:
|
| Workbook Fields | Workbook fields are summated values that are calculated at the workbook level (that take into account all activities within the workbook).
Workbook fields include:
|
| Dynamic Fields | Dynamic fields have different values depending on the context within which they are being used within an analysis.
Period Start and
Period Finish are both dynamic fields.
When Period Start and Period Finish are being applied to a phase analysis, they relate to the start and finish of the phase in question. When being used within the context of a ribbon, Period Start and Period Finish relate to the start and end date of the ribbon. |
Formulas are generally written within the context of an activity. By further exposing fields outside of the activity context (for example, project and workbook), you are able to model how activities relate to and potentially impact other contexts such as project and workbook.
Single Value Result Array Formulas
Acumen Fuse metric formulas are based on Single Value Result Array formulas. Single Value Result Array formulas work with a series of data (activities), aggregate it (typically using SUM, AVERAGE or COUNT) and return a single value to the (ribbon, phase, or intersection) analyzer.
Array formulas typically return a series of values. For example, in Excel, the formula =Row(A1:A5) returns only a single value (the first value in the list). An array formula will return all values for A1 to A5. You usually apply a container function, such as SUM or AVERAGE or COUNT, against the results of an array formula. This enables you to apply the function to the list of values and return a single value result.
In Acumen Fuse, a ribbon, phase, and intersection all contain one or more activities. In the case of phases and intersections, the activities may span across more than one phase or intersection. Therefore, certain data (for example, duration, work, and cost field types) gets prorated. When metric functions are applied during a Fuse analysis, they are applied to the ribbon, phase, or intersection indirectly being applied to all activities within that segment through the use of an array formula.
Primary Formulas
Primary formulas return any type of numeric or text-based result.
Secondary Formulas
A secondary formula is additional information shown in a ribbon/phase or intersection analysis window. Secondary formulas are defined in a similar way to primary formulas. If you want to use a secondary formula to show a percentage, set the Format field to Percentage.
There are two ways to create a secondary formula:
-
Simple percentage relative to the primary formula
If the secondary formula represents a percentage of the primary formula, then you don't need to create complex formulas. Instead, select the relevant inclusions and filters (in order to define the population against which you are going to divide the primary formula in order to calculate the percentage) and then set the mode to Percentage of Primary Formula. A simple percentage secondary formula can be auto-calculated in this mode irrespective of whether the primary formula has been defined in basic or advanced mode.
-
Advanced secondary formula
If the required secondary formula is not a simple percentage of the primary formula, then set the mode to Advanced and define the inclusions, filters and advanced formula manually.
The Two Options on the Metrics Tab
Test Metric Formulas
You can click Check Formula to validate the syntax of the formula when creating or editing a metric formula. The test calculation is applied to all activities within the workbook.
