Metric Formula Examples

Learn how to write metric formulas by following along with these examples.

Overview

Writing a metric formula requires a lot of decision-making and forethought. In this section, we'll describe the questions to ask yourself or your subject matter expert before building a metric in Visier. Then, we'll walk you through a series of different metric examples that each become more complex or build on the previous example. We will start with Headcount, a comparatively simple metric that counts current employees, and then explore more complex examples thereafter.

The standard metric formula syntax is on <subject//event> <time handling> filterBy(<filter condition>) aggregate <function>(<aggregation property>). For more information about functions you can use in a Visier metric, see Visier Formula Language (VFL).

Questions to consider

Before writing a metric formula, ask yourself the following questions:

• What business question do you want to answer? Frame this as a sentence that can be answered with a numerical value; for example, how many employees are in my organization? Who wants the answer to this question? Frame this as the core audience that will benefit from the metric, such as HR leaders, line managers, or Finance teams.
• What is the metric calculating? In Visier, a metric calculates at least one analytic object, such as Employee. For more information, see Analytic Objects.
• What type of calculation is it? In Visier, you have many aggregation options, such as count, average, and median. For more information, see Aggregation.
• Which records does it select? Usually, a metric calculates a specific attribute on the analytic object, such as gender or performance rating. For more information, see Attributes.
• How does the metric handle time? In Visier, a metric must know what records to retrieve in a time period, such as all valid records in the selected time period or records that are valid at the end of the period. For more information, see "Time handling" in Components of a metric formula.
• What data type is the metric value? Metrics can return a value as an integer, a percent, in years, in months, as a number, and more. You can configure the data type in the metric's Settings tab.
• Is the metric additive? Some metric values can be additive over any dimension or concept, like Headcount, or additive over time, like Employee Exit Count, or non-additive and cannot be summed over time, such as Average Headcount. You can configure the additivity type in the metric's Settings tab.

After answering the above questions, you can plug your answers into the standard formula syntax: on <subject//event> <time handling> filterBy(<filter condition>) aggregate <function>(<aggregation property>) .

Best practices

If using the object's default time handling, you can omit it from the formula. The platform will apply the default time handling at run time.

In metric formulas, Visier objects are referenced using their object name and path, if applicable. In the standard formula syntax, there are multiple Visier objects: the subject, the filters, and the property or dimension. To find an object's object name, open the object in a project and navigate to the Basic Information tab, then find the Object name field. Some objects have the same object name and display name, like Employee and Gender.

In the formula, you might specify an attribute's qualified name. The qualified name defines the attribute name and its parent analytic object; for example, Current Employee is an attribute of Employee, so its qualified name is Employee.Current_Employee. Use the following guidelines to decide if you should specify an object's qualified name:

• If the metric formula only uses one analytic object, you can optionally omit the attribute's qualified name because the formula already specifies the analytic object. For example, on Employee filterBy(isActiveEmployee) aggregate count(EmployeeID). Alternatively, you can write the same formula with qualified names: on Employee filterBy(Employee.isActiveEmployee) aggregate count(Employee.EmployeeID).
• If the metric formula uses multiple analytic objects, you must specify qualified names for attributes that aren't associated with the primary analytic object so that the platform knows how to execute the formula. For example, on Hire occurredIn interval aggregate average(daysBetween(Applicant.Requisition.Approved_Date, effectiveDate, gregorian)). In this formula, Hire is the primary analytic object, however, Approved_Date is associated with a different analytic object (Requisition through Applicant), so we must specify the qualified name for Approved_Date.

After creating a new metric in Visier, always preview the metric to validate it works as expected. For more information, see Preview a metric.

Headcount

This example highlights a simple metric that follows the standard formula syntax.

First, let's decide what the metric calculates and how to execute the calculation.

• We want to answer the question "How many employees are in my organization?" for HR leaders.
• Headcount calculates data from the Employee subject.
• Headcount is a count. We want to know the total number of unique employees.
• Headcount selects current employees. We don't want to count inactive or exited employees. It counts each unique employee ID for employees whose data indicates they are actively employed.
• Headcount calculates all records that meet the requirements of the formula at the end of the selected time period. If an employee left the organization at some point during the selected time period, the record doesn't meet the formula requirements and won't be included in the count.
• Headcount returns values in integers. We want a whole number without any decimals when counting our employee headcount, so integer is the data type.
• Headcount is additive because it can be summed over any dimension or concept, like summing the count of part-time employees who are women. It is not additive over time because we don't want to add the headcount from multiple time periods together; for example, Headcount in January was 300 and Headcount in February was 305. We don't want to see a Headcount value of 605 for January and February.

Next, we can plug values into the standard formula syntax: on <subject//event> <time handling> filterBy(<filter condition>) aggregate <function>(<aggregation property>)

• <subject//event>: Employee
• <time handling>: validUntil instant
• <filter condition>: isActiveEmployee
• <function>: count
• <aggregation property>: EmployeeID

Put together: 

on Employee filterBy(isActiveEmployee) aggregate count(EmployeeID)

And we're done! Preview the metric to validate that it works as expected, then publish the metric to production.

Headcount with Attribute

This example is a continuation of Headcount, but in this example we will show the value of parameterized variables in a metric. With parameterized variables, your users can dynamically select the attribute of interest to apply to the metric. In this example, we'll create a parameter that allows users to select any attribute to apply to Headcount; for example, users can use Headcount with Attribute to find out how many managers are in the organization, or how many women, or employees who are in the retirement zone, and so on.

First, let's decide what the metric calculates and how to execute the calculation.

• We want to answer the question "How many managers/women/retirement zone employees/… are in my organization?"
• Headcount with Attribute calculates data from the Employee subject.
• Headcount with Attribute is a count. We want to know the total number of unique employees that have a specific attribute.
• Headcount with Attribute selects active employees who have a user-selected attribute. The user-selected attribute is created as a parameter on the metric. In this example, after creating the Headcount with Attribute metric, we can navigate to the Parameters tab and create a member parameter called Employee_Attribute_Parameter. For more information, see Parameters (optional).
• Headcount with Attribute calculates all records that are valid at the end of the selected time period. If an employee left the organization at some point during the selected time period, we don't want to include them in the count.
• Headcount with Attribute returns values in integers. We want a whole number without any decimals when counting employee headcount, so integer is the data type.
• Headcount with Attribute is additive because it can be summed over any dimension or concept, like summing the count of employees who are managers. It is not additive over time because we don't want to add the headcount with attributes from multiple time periods together; for example, Headcount with Attribute in January was 300 and Headcount with Attribute in February was 305. We don't want to see a Headcount value of 605 for January and February when we select both months.

Next, we can plug values into the standard formula syntax: on <subject//event> <time handling> filterBy(<filter condition>) aggregate <function>(<aggregation property>)

• <subject//event>: Employee
• <time handling>: validUntil instant
• <filter condition>: isActiveEmployee && Employee_Attribute_Parameter
• <function>: count
• <aggregation property>: EmployeeID

Put together:

on Employee filterBy(isActiveEmployee && Employee_Attribute_Parameter) aggregate count(EmployeeID)

And we're done! Preview the metric to validate that it works as expected, then publish the metric to production. In the following screenshot, you can see Headcount with Attribute in action with the Manager attribute selected.

Headcount Ratio

This example is a continuation of Headcount with Attribute. In this example, we'll create Headcount Ratio as a derived metric from Headcount with Attribute. We create derived metrics to organize related metrics and use features unique to derived metrics, like derived metric settings. For more information about derived metrics, see Derived metrics (optional).

To create Headcount Ratio as a derived metric of Headcount with Attribute, do the following:

1. In a project, on the navigation bar, navigate to Model > Metrics.
2. Search for and select Headcount with Attribute.
3. In Headcount with Attribute, navigate to Derived Metrics.
4. Click Create Derived Metric.
5. In Create derived metric, in Display name, type Headcount Ratio.
6. In Derived metric type, select Rate.
7. In Description, type The number of employees with a particular employee attribute at the end of the period.
8. When finished, click Create.

Headcount Ratio is a rate calculation, which means it's calculated with a numerator and denominator. Because Headcount Ratio is derived from Headcount with Attribute, it inherits Headcount with Attribute's formula, which becomes Headcount Ratio's numerator.

To set the metric's denominator:

1. In Headcount Ratio, navigate to Settings.
2. In Denominator, select Headcount. This means that Headcount Ratio is calculated by dividing Headcount with Attribute by Headcount.

Additionally, in Settings, set the Data type to Percent.

Now that we understand a derived metric's numerator and denominator, let's go through our questions about what the metric calculates and how to execute the calculation.

• We want to answer the question "What percentage of employees in my organization are managers/women/retirement zone employees…?"
• Headcount Ratio calculates data from the Employee subject.
• Headcount Ratio is a rate. We want to know the percentage of employees that have a specific attribute compared to the overall employee population.
• Headcount Ratio selects active employees who have a user-selected attribute and divides them by the overall active employee population.
• Headcount Ratio calculates all records that are valid at the end of the selected time period. If an employee left the organization at some point during the selected time period, we don't want to include them in the ratio.
• Headcount Ratio returns values as a percentage. We want a percent so we can represent the proportion of employees with a specific attribute compared to the overall headcount.

• Headcount Ratio is non-additive because it can't be summed over any dimension, concept, or time; for example, Headcount Ratio (Employee Attribute: Manager) in January was 27% and in February it was 30%. We don't want to see a Headcount Ratio value of 57% for January and February when we select both months.

  Tip: Ratio metrics are always non-additive!

We don't have to do anything to the metric formula for a derived metric. A derived metric inherits its parent's formula. In rate metrics like Headcount Ratio, the parent's formula becomes the numerator in the calculation, and you can select the denominator in the Settings tab, which we did earlier in this example.

And we're done! Preview the metric to validate that it works as expected, then publish the metric to production.

Exit After Selected Period

This example is actually a calculated property that has a formula. In the formula, we'll highlight the aggregate function exists(property, number). We'll then use the calculated property to create a selection concept that can then be used as a group by with other metrics, like Headcount. This combination of calculated property, selection concept, and metric give us the ability to select a time period to find out which group had the most exits after the selected time.

A calculated property evaluates each subject member individually to check if the member meets the formula criteria. In this example, the calculated property is created on the Employee subject and evaluates each employee to check if they have an exit event after the selected time period. For example, let's say you selected February 2023. The calculated property evaluates each employee to check if they exited after February 2023.

First, create the Exit After Selected Period calculated property on Employee. For more information, see Properties. You can leave Formula blank for now.

To write the calculated property formula, let's decide what the calculated property calculates.

• We want to answer the question "Which departments had the most exits after the selected time period?"
• Exit After Selected Period calculates data from the Employee Exit event.
• Exit After Selected Period is an aggregation that evaluates whether an Employee Exit event occurred on each Employee. To check if an event exists, we can use the function exists(property, number). In this example, the property to check is EmployeeID and the number is 1. This means that the calculation checks each employee ID to see if there is at least 1 exit event after the selected time.
• Exit After Selected Period does not select any records. It evaluates records to determine whether they meet the formula criteria. In this example, we will make a selection concept, Has Exit After Selected Period, that selects employees who are active during the selected period but have an exit event after the period (employees whose Exit After Selected Period property is true).
• Exit After Selected Period evaluates active employees at a selected time period in the past, for example, February 2023, and then evaluates by looking forward in time to see if an employee exited the organization. To allow selecting a time period in the past, we can use the function occurredIn interval(instant, latest). This time handling function means that it evaluates records that were valid at any time during the selected period (occurredIn interval) and look forward to the latest instant of data to evaluate the formula (instant, latest).
• Exit After Selected Period returns values as a Boolean. We want to know whether a member satisfies the formula criteria: if true, an employee has an exit event after the selected period. You can set the data type in the calculated property's Customize tab.
• Exit After Selected Period does not have an additivity setting because it's not a metric. It evaluates on individual employee records and returns a Boolean value for the employee.

Next, we can plug values into the standard formula syntax: on <subject//event> <time handling> filterBy(<filter condition>) aggregate <function>(<aggregation property>)

• <subject//event>: Employee_Exit
• <time handling>: occurredIn interval(instant, latest)
• <filter condition>: isActiveEmployee
• <function>: exists
• <aggregation property>: (EmployeeID, 1)

Put together:

on Employee_Exit occurredIn interval(instant, latest) aggregate exists(EmployeeID,1)

To finish this example, let's talk about how you can use the calculated property.

1. Create a calculated selection concept with the display name Has Exit After Selected Period. In the formula, type Employee.Exit_After_Selected_Period. This means that the concept will select any employee record whose Exit After Selected Period property is true. For more information about creating concepts, see Create a Selection Concept.
2. In the solution experience, in Explore, set the visual title to Breakdown of Headcount by Organization.
3. In the Time picker, select a time period in the past.
4. In the Filter picker, select Has Exit After Selected Period.

   Result: You can see the number of employees that exited the organization after the selected time period for each department. For example, if Marketing in February 2023 is 138, that means that 138 employees in Marketing exited the organization since February 2023. In the Info panel, in the Insights tab, click View details to see which employees exited after the selected period.

Tenure

Like the previous example, this example is actually a calculated property that has a formula. In the formula, we'll highlight the function monthsBetween().

We'll then use the calculated property to create a metric that calculates the average tenure of employees in years. In this example, we create Tenure as a calculated property so that we can store Tenure as a property on the Employee subject. If we only wrote tenure into a metric formula without a calculated property, we couldn't see an employee's tenure in the Detailed View visual.

A calculated property evaluates each subject member individually to check if the member meets the formula criteria. In this example, the calculated property is created on the Employee subject and evaluates each employee to calculate their tenure in months (so that we can show the monthsBetween() function).

First, create the Tenure calculated property on Employee. For more information, see Properties. You can leave Formula blank for now.

To write the calculated property formula, let's decide what the calculated property calculates.

• We want to answer the question "How long has an employee worked at the organization?".
• Tenure (calculated property) calculates data from the Employee subject. However, it's created as a property on the Employee subject, so we don't need to include the on Employee statement in the formula.
• Tenure (calculated property) is a months between calculation. We want to know number of months between an employee's start date and the current date in the Gregorian calendar.
• Tenure (calculated property) doesn't select any records. It evaluates employee records to calculate the time between the current date and the employee's start data at the organization.
• Tenure (calculated property) doesn't have a time handling function because it's calculating the time between two set dates in the employee data.
• Tenure (calculated property) returns values in months. You can set the data type in the calculated property's Customize tab.
• Tenure (calculated property) does not have an additivity setting because it's not a metric. It evaluates on individual employee records and returns a value in months for the employee.

For the Tenure calculated property, we will use the monthsBetween syntax instead of the standard metric syntax: monthsBetween(property, property, number). For more information, see monthsBetween.

• property: Start_Date
• property: effectiveDate
• number: gregorian

Put together:

monthsBetween(Start_Date, effectiveDate, gregorian)

To finish this example, let's talk about how you can use the calculated property.

1. Create a metric with the display name Tenure. In the formula, type on Employee filterBy(isActiveEmployee) aggregate average(Tenure/12). This means that the metric calculates the average tenure of current employees at the end of the selected period and divides that value by 12. Because the Tenure calculated property is calculated in months, we divide the metric value by 12 to get a value in years. In the metric settings, set the Data type to Years and the Additivity type to Non-additive. Tenure is non-additive because it can't be summed over any concept, dimension, or time; for example, Tenure in January was 5.4 years and Tenure in February was 6.2 years. We don't want to see a Tenure value of 11.6 years for January and February when we select both months.
2. In the solution experience, in Explore, set the visual title to Breakdown of Tenure by Organization.
3. In the Time picker, select a time period that has employee data.

   Result: You can see the average number of years that employees have worked at the organization as of the selected time period for each department. For example, if Marketing in February 2023 is 1.3, that means active Marketing employees in February 2023 have worked at the organization for an average of 1.3 years. In the Info panel, in the Insights tab, click View details to see the Tenure for each Marketing employee, as long as Tenure is configured to appear in the Detailed View visual. For instructions on how to add properties to Detailed View, see Configure View Details.

Time in Job Family

Like the previous example, this example is actually a calculated property that has a formula. In the formula, we'll highlight the function currentStateStart().

The currentStateStart() function evaluates values to understand the period length that the value has been in that state. It evaluates on text, number, and Boolean data types. Expanding on what we just learned about monthsBetween() , we will use monthsBetween() and currentStateStart() to evaluate each employee individually to understand the length of time the employee has been in their current job family.

We'll then use the calculated property to create a metric that calculates the average time in job family of all employees in months. In this example, we create Time in Job Family as a calculated property so that we can store Time in Job Family as a property on the Employee subject. If we only wrote time in job family into a metric formula without a calculated property, we couldn't see an individual employee's time in job family in the Detailed View visual.

First, let's decide what the calculated concept calculates and how to execute the calculation.

• We want to answer the question "How long has the employee been in their current job family?".
• Time in Job Family (calculated property) calculates data from the Employee subject. However, it's created as a property on the Employee subject, so we don't need to include the on Employee statement in the formula.
• Time in Job Family (calculated property) is a months between calculation (monthsBetween()). We want to know number of months between an employee's start date and the effective date in the Gregorian calendar. Additionally, it's a current state start calculation (currentStateStart()). We want to take the employee's current state in a particular property (in this example, Job Family) and calculate the months between the start of that state and the effective date in the Gregorian calendar to find how long the employee has been in that state.
• Time in Job Family (calculated property) selects the Job Family (Job_Family) record.
• Time in Job Family (calculated property) doesn't have a time handling function because it's calculating the time between two set dates in the employee data.
• Time in Job Family (calculated property) returns values in months. You can set the data type in the calculated property's Customize tab.
• Time in Job Family (calculated property) does not have an additivity setting because it's not a metric. It evaluates on individual employee records and returns a value in months for the employee.

For the Time in Job Family calculated property, we will use the monthsBetween syntax instead of the standard metric syntax: monthsBetween(property, property, number). For more information, see monthsBetween.

• property: currentStateStart(property)
  • property: Job_Family
• property: effectiveDate
• number: gregorian

All together:

monthsBetween(currentStateStart(Job_Family), effectiveDate, gregorian)

To finish this example, let's talk about how you can use the calculated property.

1. Create a metric with the display name Time in Job Family. In the formula, type on Employee filterBy(isActiveEmployee) aggregate average(Time_in_Job_Family). This means that the metric calculates the average time that current employees have spent in their current job family at the end of the selected period. In the metric settings, set the Data type to Months and the Additivity type to Non-additive. Tenure is non-additive because it can't be summed over any concept, dimension, or time; for example, Time in Job Family in January was 5.4 months and Time in Job Family in February was 6.2 months. We don't want to see a Time in Job Family value of 11.6 months for January and February when we select both months.
2. In the solution experience, in Explore, set the visual title to Breakdown of Time in Job Family by Organization.
3. In the Time picker, select a time period that has employee data.

   Result: You can see the average number of months that employees have spent in their current job family as of the selected time period for each department. For example, if Marketing in February 2023 is 1.3, that means active Marketing employees in February 2023 have been in their current job families for an average of 1.3 months. In the Info panel, in the Insights tab, click View details to see the Time in Job Family for each Marketing employee, as long as Time in Job Family is configured to appear in the Detailed View visual. For instructions on how to add properties to Detailed View, see Configure View Details.

Overtime to Regular Hours Worked Ratio

Like the previous example, this example is actually a calculated property that has a formula. In the formula, we'll use two other calculated properties to calculate a ratio. There's several layers to this calculated property, starting with two selection concepts that are then used in two calculated properties.

First, let's figure out what we need to know before we can calculate the percentage of overtime hours that your employees work compared to regular hours. We must know each employee's regular worked hours and overtime hours. To know that, the data that you send to Visier must have a column that identifies the type of work hours your employees completed; for example, a column called Hours Recorded with values such as OT, REG, and PTO.

To give the data values meaning in Visier, we can use selection concepts to identify certain values as "Regular Hours" or "Overtime Hours". In this example, we'll have two selection concepts, as follows:

• Regular Hours (object name: isRegularHours): Selects the REG value.
• Overtime Hours (object name: isOvertimeHours): Selects the OT value.

We can use these selection concepts in calculated property formulas to calculate the amount of regular hours and overtime hours that each employee works. In this example, we'll have two calculated properties created on the Employee subject, as follows:

• Regular Hours (object name: Regular_Hours): Calculates the regular hours that an employee worked. The formula is on Hours occurredIn periodOf(instant) filterBy isRegularHours aggregate sum(Hours.Hours). In the Customize tab, the data type is Hours.
• Overtime Hours (object name: Overtime_Hours): Calculates the overtime hours that an employee worked. The formula is on Hours occurredIn periodOf(instant) filterBy isOvertimeHours aggregate sum(Hours.Hours). In the Customize tab, the data type is Hours.

Now that we're able to calculate an employee's regular worked hours and overtime worked hours, we can calculate the ratio of overtime hours to regular hours. Typically, a ratio is one value divided by a different value. In this example, we want to divide overtime hours by the number of regular hours.

Next, let's decide how to calculate the calculated property and how to execute the calculation.

• We want to answer the question "What is the ratio for each employee's overtime to regular work hours?".
• Overtime to Regular Worked Hours Ratio calculates a value for each employee, so it is created on the Employee subject.
• Overtime to Regular Worked Hours Ratio is a ratio calculation that uses the calculated properties Overtime Hours and Worked Hours. We want to know the percentage of overtime hours an employee has worked compared to regular hours.
• Overtime to Regular Worked Hours Ratio doesn't have a time handling function because it calculates a ratio.
• Overtime to Regular Worked Hours Ratio returns values in percent. You can set the data type in the calculated property's Customize tab.
• Overtime to Regular Worked Hours Ratio does not have an additivity setting because it's not a metric. It evaluates on individual employee records and returns a value in percent for the employee.

For the Overtime to Regular Worked Hours Ratio calculated property, we will divide two properties instead of using the standard metric syntax.

All together:

(Employee.Overtime_Hours) / (Employee.Worked_Hours)

To finish this example, let's talk about how you can use the calculated property.

1. Create a metric with the display name Average Employee Overtime to Regular Worked Hours Ratio. In the formula, type on Employee filterBy(isActiveEmployee) aggregate average(overtime_to_regular_hours_ratio). This means that the metric calculates the average percentage of hours that current employees worked overtime compared to regular hours at the end of the selected period. In the metric settings, set the Data type to Percent and the Additivity type to Non-additive. Average Employee Overtime to Regular Worked Hours Ratio is non-additive because it can't be summed over any concept, dimension, or time; for example, Average Employee Overtime to Regular Worked Hours Ratio in January was 25% and Average Employee Overtime to Regular Worked Hours Ratio in February was 13%. We don't want to see a Average Employee Overtime to Regular Worked Hours Ratio value of 38% for January and February when we select both months.
2. In the solution experience, in Explore, set the visual title to Breakdown of Average Employee Overtime to Regular Worked Hours Ratio by Organization.
3. In the Time picker, select a time period that has employee data.

   Result: You can see the average percentage that employees worked overtime compared to regular hours as of the selected time period for each department. For example, if Marketing in February 2023 is 20%, that means active Marketing employees in February 2023 worked, on average, 20% more than their regular hours. In the Info panel, in the Insights tab, click View details to see the Overtime to Regular Worked Hours Ratio for each Marketing employee, as long as Overtime to Regular Worked Hours Ratio is configured to appear in the Detailed View visual. For instructions on how to add properties to Detailed View, see Configure View Details.