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 Visier Formula Language (VFL).
• 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. For more information, see Settings (required).

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 Visier Formula Language (VFL).

• 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 Visier Formula Language (VFL).

• 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.

Average Headcount

This example highlights the time handling function forInstantsInInterval() and the aggregate function averageOverTime().

The function forInstantsInInterval() only works for subjects. It finds records in the specified time interval at the specified period boundaries to be used only with aggregation functions like sumOverTime and averageOverTime.

The aggregate function averageOverTime produces the average of a measure over a time interval, approximating the integral using the trapezoid rule on the instants specified by the time filter forInstantsInInterval or forIntervalsInInterval.

Let's say that we're looking at a Trend visual for Headcount in January, February, March, and April 2023.

• January 2023: 962
• February 2023: 965
• March 2023: 969
• April 2023: 962

Average Headcount uses forInstantsInInterval() and averageOverTime to calculate the average headcount over a period of time. In this example, the time granularity is month. Let's say we want to know the average headcount in February 2023.

We can calculate February 2023's average headcount by adding January 2023 + February 2023 and dividing by two (the number of months we included in the average): (962 + 965) / 2 = 963.5.

Next, let's walk through what the metric calculates and how to execute the calculation using VFL.

• We want to answer the question "On average, how many employees are in my organization?".
• Average Headcount calculates data from the Employee subject.
• Average Headcount is an average over time. We want to know the average number of employees over a specific time period.
• Average Headcount selects active employees. We don't want to average inactive employees. It calculates the average using the trapezoid rule on Employee IDs for active employees.
• Average Headcount 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 that employee in the average.
• Average Headcount returns values in numbers. It's fine to have a decimal number when averaging our employee headcount, so number is the data type.
• Average Headcount is non-additive because it can't be summed over any dimension or concept. It is not additive over time because we don't want to add the Average Headcount from multiple time periods together; for example, Average Headcount in January was 156.5 and Average Headcount in February was 159.5. We don't want to see an Average Headcount value of 316 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>: forInstantsInInterval(interval, month)
• <filter condition>: isActiveEmployee
• <function>: averageOverTime
• <aggregation property>: EmployeeID

Put together:

on Employee forInstantsInInterval(interval, month) filterBy(isActiveEmployee) aggregate averageOverTime(count(EmployeeID))

And we're done! Preview the metric to validate that it works as expected, then publish the metric to production. To validate, you can look at a Trend of Average Headcount compared to Headcount for a specific time period; for example, January and February 2023. For more information about comparing metrics, see Create a Metric Comparison.

Future Work Anniversary

This example highlights using a nested query in a metric formula. A nested query uses the := function to assign intermediary calculated properties and metrics within the formula. Intermediary properties and metrics only exist in the formula that they’re written in; you cannot reference an intermediary property or metric in a different object’s formula.

In this example, we use := to assign a Future_Work_Anniversary_Date intermediary calculated property that evaluates each employee's start date. The user can dynamically shift the start date by a specific year to find anyone who has a work anniversary coming up in the future.

To accomplish this, we'll define Future_Work_Anniversary_Date using:

• The shift() function to shift the start date forward.
• A metric parameter called Anniversary_Length_Parameter dynamically assign the number of years to shift by. In this example, after creating the Future Work Anniversary metric, we can navigate to the Parameters tab and create a numeric parameter called Anniversary_Length_Parameter. For more information, see Parameters (optional).

Then, in the metric's main formula, we can use Future_Work_Anniversary_Date in the time handling statement.

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

• We want to answer the question "Which employees have their 5 year work anniversary next year?".
• Future Work Anniversary calculates data from the Employee subject.
• Future Work Anniversary is a count. We want to know the total number of upcoming work anniversaries for employees.
• Future Work Anniversary selects active employees. We don't want to count anniversaries for inactive employees.
• Future Work Anniversary calculates all records that are valid during the selected time period. If an employee left the organization, we don't want to count future work anniversaries for those employees.
• Future Work Anniversary returns values in integers. We want a whole number without any decimals when counting upcoming work anniversaries, so integer is the data type.

• Future Work Anniversary is additive over time because it can be summed over time, dimensions, and concepts. For example, we can add the future work anniversaries from multiple time periods together; for example, Future Work Anniversary filtered by Women in January is 25 and Future Work Anniversary filtered by Women in February is 15. We want to see a Future Work Anniversary filtered by Women value of 40 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>: occurredIn(interval, <date attribute>), where <date attribute> is the Future_Work_Anniversary_Date intermediary calculated property
• <filter condition>: isActiveEmployee
• <function>: count
• <aggregation property>: EmployeeID

Put together:

Future_Work_Anniversary_Date := property(on Employee validUntil instant aggregate(max(shift(Start_Date, years(Anniversary_Length_Parameter), gregorian))))

on Employee occurredIn(interval, Future_Work_Anniversary_Date) 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.

Manager Instability Count

This metric formula shows how to use a nested query in a calculated property. In this example, we use := to assign a managerChangeCount intermediary calculated property.

In addition to :=, this example shows the functions previousChanges() and hasChanged(). The previousChanges() function complements hasChanged() by evaluating whether a previous data record had a change. In this example, we use previousChanges() to specify that we want to check changes on the Employee subject and we use hasChanged() to specify that we’re looking for changes to the Direct Manager ID property.

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

• We want to answer the question "How many employees had at least one manager change during the period?".
• Manager Instability Count calculates data from the Employee subject.
• Manager Instability Count is a count. We want to know the total number of unique employees who had at least one manager change.
• Manager Instability Count selects employees who had at least one manager change. We don't want to count employees who had the same manager during the period.
• Manager Instability Count calculates all records that occurred during the time period. If an employee's direct manager ID changed in December compared to the previous ID, the employee is counted once for December.
• Manager Instability Count returns values in integers. We want a whole number without any decimals when counting our employee manager instability, so integer is the data type.
• Manager Instability Count is additive because it can be summed over any dimension or concept, like summing the manager instability of part-time employees who are women. It is not additive over time because we don’t want to add the Manager Instability Count from multiple time periods together; for example, Manager Instability Count in January was 24 and Manager Instability Count in February was 16. We don't want to see a Manager Instability Count of 40 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>: managerChangeCount
• <function>: count
• <aggregation property>: EmployeeID

Put together:

managerChangeCount := property(on previousChanges(Employee) occurredIn periodOf(instant) filterBy (hasChanged(Direct_ManagerID) && isActiveEmployee && !conception) aggregate count(EmployeeID))

on Employee filterBy(managerChangeCount > 0) aggregate count(EmployeeID)

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

Total Goals Ending in Period

This example highlights a metric that allows users to filter with dates other than the effective date. In this example, we want to select all goals that have an end date between the start of the time period and the end of the time period. In the formula, we can filter by the End Date property of the Employee Goals subject where the goal end date is greater than the start of the period but less than the end of the time.

Let's decide what the metric calculates and how to execute the calculation.

• We want to answer the question "How many goals are expiring during the period?".
• Total Goals Ending in Period calculates data from the Employee Goals subject.
• Total Goals Ending in Period selects all employees that have a goal expiring.
• Total Goals Ending in Period calculates all records that are valid at the end of the selected time period.
• Total Goals Ending in Period returns values in integers. We want a whole number without any decimals when counting our employee total goals ending in period, so integer is the data type.
• Total Goals Ending in Period is additive over time because we want to add the total goals ending in a period from multiple time periods together. For example, Total Goals Ending in Period filtered by Women in January is 300 and Total Goals Ending in Period filtered by Women in February is 305. We want to see a Total Goals Ending in Period filtered by Women 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_Goals
• <time handling>: validUntil instant
• <filter condition>: Employee_Goals.End_Date >= start(periodOf(instant)) && Employee_Goals.End_Date < end(periodOf(instant))
• <function>: count
• <aggregation property>: Employee_GoalsID

Put together:

on Employee_Goals filterBy(End_Date >= start(periodOf(instant)) && End_Date < end(periodOf(instant))) aggregate count(Employee_GoalsID)

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

Headcount Active During Period

This example highlights the time handling function lastKnownStateByFilterIn that evaluates if a state was ever valid during the period.

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

• We want to answer the question "How many employees were active at any time during the period in my organization?".
• Headcount Active During Period calculates data from the Employee subject.
• Headcount Active During Period is a count. We want to know the total number of unique employees that were active at any time during the period.
• Headcount Active During Period selects employees who were active at any time during the period. If an employee was active but left the organization during the period, the metric counts that employee as active during the period.
• Headcount Active During Period calculates all records that are valid during the selected time period.
• Headcount Active During Period returns values in integers. We want a whole number without any decimals when counting our employee headcount active during the period, so integer is the data type.
• Headcount Active During Period is additive because it can be summed over any dimension or concept, like summing the headcount of active 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 Active During Period in January was 300 and Headcount Active During Period in February was 305. We don't want to see a Headcount Active During Period 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>: lastKnownStateByFilterIn(<inactive filter>, <time filter>) where <inactive filter> is !isActiveEmployee and <time filter> is interval

  Note: lastKnownStateByFilterIn looks for inactive records (!isActiveEmployee) to check if the start of the inactive state is within the selected time period. If the metric only looked for isActiveEmployee, it wouldn't count records that changed from active to inactive during the period. The function uses interval to scan through the selected time period to find any employee that was active at any time during the period.

• <filter condition>: !isActiveEmployee
• <function>: count
• <aggregation property>: EmployeeID

Put together:

on Employee lastKnownStateByFilterIn(!isActiveEmployee, interval) aggregate count(EmployeeID)

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

Retirement Count

This example highlights a metric based on an event. The previous examples, excluding Exit After Selected Period, were metrics based on subjects. An event occurs at a specific point in time, whereas a subject is active across time. To write a metric formula based on an event, we can follow the same steps as metrics based on a subject.

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

• We want to answer the question "How many employees left my organization due to retirement?".
• Retirement Count calculates data from the Employee Exit event.
• Retirement Count is a count. We want to know the total number of unique employees that retired from the organization.
• Retirement Count selects employees whose exit reason is "Retirement". We don't want to count employees who exited for non-retirement reasons, like resignation or termination.
• Retirement Count calculates all records that occurred at a selected point in time. If an employee retired in February, we want to include them in February's Retirement Count.
• Retirement Count returns values in integers. We want a whole number without any decimals when counting our employee retirements, so integer is the data type.
• Retirement Count is additive over time because it can be summed over time, dimensions, and concepts. For example, Retirement Count filtered by Women in January is 300 and Retirement Count filtered by Women in February is 305. We want to see a Retirement Count filtered by Women 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_Exit
• <time handling>: occurredIn interval
• <filter condition>: isExitActualRetirement
• <function>: count
• <aggregation property>: EmployeeID

Put together:

on Employee_Exit filterBy(isExitActualRetirement) aggregate count(EmployeeID)

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

Open Requisition Count

This example shows how to join data from two analytic objects using references. In this example, we want to count the number of open requisitions that have more than 5 applicants. This requires data from two subjects: Requisition and Applicant. To accomplish this in the metric formula, we use a nested query to create an intermediate calculated property called NumberOfApplicantsForRequisition. In the nested query, the formula is on Applicant and uses the via function to call Applicant's reference, Requisition. This relies on a "reference" relationship between Applicant and Requisition. For more information, see References.

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

• We want to answer the question "How many open requisitions have 5 or more applicants at the end of the period?".
• Open Requisition Count calculates data from the Requisition subject.
• Open Requisition Count is a count. We want to know the total number of unique requisitions that have 5 or more applicants.
• Open Requisition Count selects open requisitions. We don't want to count closed requisitions.
• Open Requisition Count calculates all records that are open at the end of the selected time period. If a requisition was closed at the end of the selected time period, we don't want to include it in the count.
• Open Requisition Count returns values in integers. We want a whole number without any decimals when counting open requisitions, so integer is the data type.
• Open Requisition Count is additive because it can be summed over any dimension or concept, like summing the count of open requisitions for full-time regular positions. It is not additive over time because we don't want to add the open requisition count from multiple time periods together; for example, Open Requisition Count in January was 300 and Open Requisition Count in February was 305. We don't want to see an Open Requisition Count 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>: Requisition
• <time handling>:validUntil instant
• <filter condition>: NumberOfApplicantsForRequisition > 5 && isActiveEmployee
• <function>: count
• <aggregation property>: RequisitionID

Put together:

NumberOfApplicantsForRequisition := property(on Applicant via Requisition validUntil instant aggregate count(Applicant.ApplicantID))

on Requisition filterBy(NumberOfApplicantsForRequisition > 5 && isOpenRequisition) aggregate count(RequisitionID)

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