Visier Formula Language (VFL)
Use the Visier Formula Language (VFL) to define metrics in the Visier platform.
This is a complete list of the syntax and functions you can use to define your metrics. Each definition includes a description, real-life examples, and acceptable parameter types.
Tip: To learn more, take the Visier University eLearning course: Using Visier Formula Language.
Analytic Object
Analytic objects are the "things" that contain specific data. These objects represent what you want to analyze or aggregate data for, like applicants, employees, pay change events, interviews, and even employee skills. For more information, see Analytic Objects.
Event Transform
Use analytic object event transform functions to create different views for events. For example, retrieving an event state from a previous point in time.
priorState
Creates a prior-state view on an event. The resulting data points will be based on the state prior to the event occurrence, as opposed to the state after the event occurrence.
Examples
Get the median pay level of employees prior to promotion for promotions that occurred in the selected time interval.
Parameter types: (Event)
on priorState(Promotion) occurredIn(interval) aggregate(median(Promotion.Employee.PayLevel))Subject State Changes
Use functions for analytic object subject state changes to query the states that a subject goes through. For example, calculating the number of employees that have received an increase in pay by comparing the previous pay level to the current pay level.
changes
Creates an event view on a subject that returns each state change as a pair of events. One for the change-from event, i.e. change from the previous state, and another for the change-to event, i.e. change to the next state.
Examples
Get the number of employee state change events that occurred in the context time interval.
Parameter types: (Subject)
on changes(Employee) occurredIn(interval) aggregate(count(Employee.EmployeeID))conception
A change event filter for conception events. A conception event is a state with no immediate preceding state. Use conception to select records that had their first state in the time context. This is only valid for change views that include change-from events, such as previousChanges.
Examples
Get the number of new hires in the context time interval.
on previousChanges(Employee) occurredIn(interval) filterBy(conception) aggregate(count(Employee.EmployeeID))hasChanged
hasChanged(Hierarchy)
Creates a filter for changes where the value for a hierarchy changed.
Examples
Get the number of times employees have changed their location.
Parameter types: (Hierarchy)
on previousChanges(Employee) occurredIn(interval) filterBy(hasChanged(Employee.Location)) aggregate(count(Employee.EmployeeID))hasChanged(Property)
Creates a filter for changes where the property value changed.
Examples
Get the number of times employees have had a change in manager.
Parameter types: (Property)
on previousChanges(Employee) occurredIn(interval) filterBy(hasChanged(Employee.ManagerID)) aggregate(count(Employee.EmployeeID))next
next(Property)
Creates a property that calculates on the next state of a change-to event. This is only valid for change views that include change-to events.
Examples
Get the number of employee pay level increases.
Parameter types: (Property)
on nextChanges(Employee) occurredIn(interval) filterBy(Employee.PayLevel < next(Employee.PayLevel)) aggregate(count(Employee.EmployeeID))next(Filter)
Creates a filter that is applied on the next state of a change-to event. This is only valid for change views that include change-to events.
Examples
Get the number of employee movements to critical roles.
Parameter types: (Filter)
on nextChanges(Employee) occurredIn(interval) filterBy(!IsCritical && next(IsCritical)) aggregate(count(Employee.EmployeeID))nextChanges
Creates an event view on a subject that returns each change-to event.
Examples
Get the number of employee change-to events that occurred in the context time interval.
Parameter types: (Subject)
on nextChanges(Employee) occurredIn(interval) aggregate(count(Employee.EmployeeID))previous
Creates a filter on the previous state of a change-from event.
Note:
- Returns unknown for conception events, termination events, and nextChanges.
- Throws an exception when used with any change views that do not include change-from events or trying to use 'previous' as the parameter value (e.g., previous(previous(filter)).
Examples
Get the number of employees that have received an increase in pay.
Parameter types: (Property)
on previousChanges(Employee) occurredIn(interval) filterBy(previous(Employee.PayLevel) < Employee.PayLevel) aggregate(count(Employee.EmployeeID))Get the number of employees that changed from non-critical to critical.
Parameter types: (Filter)
on previousChanges(Employee) occurredIn(interval) filterBy(previous(!IsCritical) && IsCritical) aggregate(count(Employee.EmployeeID))previousChanges
Returns event-like results where each event records a change from the previous state.
Note:
- Cannot be used with events or overlays.
- Use previous to retrieve the subject's state just before the change.
Examples
Get the number of changes on the Employee subject that occurred during the selected time period.
Parameter types: (Subject)
on previousChanges(Employee) occurredIn(interval) aggregate(count(Employee.EmployeeID))termination
Creates a filter for the termination event. This is only valid for change events that include change-to events. Additionally, you can use termination as the end condition for traversals.
Note:
- Termination change event occurs if the end state of a subject member does not have an immediate and subsequent state.
- The termination event date is the same as the "validityEnd" date.
Examples
Get the employees that have an upcoming termination event.
on nextChanges(Employee) occurredIn(interval) filterBy(termination) aggregate(count(Employee.EmployeeID))If the subject's final state is "Inactive" until the end of time, you won't be able to retrieve terminations using the termination function. Instead, you can filter by "isInactive".
on previousChanges(Employee) filterBy isInactive and not previous(isInactive) aggregate count(EmployeeID)Subject State Traversals
Use functions for analytic object subject state traversals to capture the state changes a subject goes through. For example, calculate the number of applicants that have moved from "Interviewing" to "Hired".
excluding
Specifies the condition used for excluding traversal time when using traversals. Any time spent in a traversal while the condition is met is excluded.
Examples
Get the average number of days between the Interviewing stage and Hired stage, excluding time on hold.
Parameter types: (Filter)
on traversals(Applicant) from(Interviewing) to(Hired) excluding(OnHold) occurredIn(interval) aggregate(average(days(traversalDuration)))from
Specifies the start condition for traversals, and typically pairs with to which specifies the end condition.
Examples
Get the number of applicants that moved from the Interviewing stage to the Hired stage during the selected interval.
Parameter types: (Value)
on traversals(Applicant) from(Interviewing) to(Hired) occurredIn(interval) aggregate(count(Applicant.ApplicantID))starting
Creates a property that calculates on the starting state of a traversal, as opposed to the ending state of a traversal.
Examples
Get the number of applicants that have progressed by at least one stage.
Parameter types: (Property)
on traversals(Applicant) from(hasValue(Applicant.Stage)) to(Applicant.Stage > starting(Applicant.Stage)) aggregate(count(Applicant.ApplicantID))termination
A traversal ending condition for the termination state. A termination change event occurs if the end state of a subject member does not have an immediate and subsequent state.
Examples
Get the number of applicants that traversed from Interviewing to Rejected or termination.
on traversals(Applicant) from(Interviewing) to(Rejected || termination) aggregate(count(Applicant.ApplicantID))to
Specifies the end condition for traversals, and typically pairs with from which specifies the start condition.
Examples
Get the number of applicants that moved from Interviewing to Hired during the selected time interval.
Parameter types: (Value)
on traversals(Applicant) from(Interviewing) to(Hired) occurredIn(interval) aggregate(count(Applicant.ApplicantID))Get the number of applicants that moved from Interviewing to Hired or Rejected during the selected time interval.
Parameter types: (Value)
on traversals(Applicant) from(Interviewing) to(Hired || Rejected) occurredIn(interval) aggregate(count(Applicant.ApplicantID))Get the number of applicants that moved from Interviewing to Hired or Rejected during the selected time interval.
Parameter types: (Value)
on traversals(Applicant) from(Interviewing) to(!Interviewing || termination || instant) occurredIn(interval) aggregate(average(days(traversalDuration)))traversalDuration
Creates a property that returns the duration of the traversal in milliseconds. If the traversal query has an exclusion clause, the resulting duration will exclude time while the exclusion condition is met.
Note: This can only be used in a traversal query.
Examples
Get the average number of days it takes applicants to move from Interviewing to Hired.
on traversals(Applicant) from(Interviewing) to(Hired) aggregate(average(days(traversalDuration)))Get the average number of days it takes applicants to move from Interviewing to Hired, excluding any time that the applicant was on hold.
on traversals(Applicant) from(Interviewing) to(Hired) excluding(OnHold) aggregate(average(days(traversalDuration)))traversalDurationWorkingDays
Creates a property that returns the working days duration of the traversal in milliseconds. If the traversal query has an exclusion clause, the resulting duration will exclude time while the exclusion condition is met.
Note: This can only be used in a traversal query.
Examples
Get the average number of working days it takes applicants to move from Interviewing to Hired.
on traversals(Applicant) from(Interviewing) to(Hired) aggregate(average(days(traversalDurationWorkingDays)))Get the average number of working days it takes applicants to move from Interviewing to Hired, excluding any time that the applicant was on hold.
on traversals(Applicant) from(Interviewing) to(Hired) excluding(OnHold) aggregate(average(days(traversalDurationWorkingDays)))traversals
Keyword for starting a traversal view on a subject. A traversal is an event that occurs upon a successful transition from a starting state to an ending state, with potentially multiple successive intermediary states in between. The starting and ending states are specified through the accompanying from and to clauses.
Examples
Get the number of applicants that moved from Interviewing to Hired in the selected time interval.
Parameter types: (Subject)
on traversals(Applicant) from(Interviewing) to(Hired) occurredIn(interval) aggregate(count(Applicant.ApplicantID))Collections
Use collection functions to create a list of values. For example, you can add a list of regions to a filter so that the query results only show employees with a region that matches a value in your list.
list
Creates a list of values.
Examples
Create a list of department names.
Parameter types: (Values)
list("R&D", "Products")Create a list of locations with each location being added a new list that contains the country, state or province, and city.
Parameter types: (Values)
list(list("Canada", "BC", "Vancouver"), list("USA", "WA", "Seattle"))listAny
Create a list that ignores any invalid symbols. This is used to define an exhaustive list that may contain symbols that are not always valid. This is typically used with sumIfAnyExists(Values) or collectFirstIfAnyExists(Values).
Examples
Create a list of compensation properties for different types of compensations, some of which may not exist or may not be valid.
Parameter types: (Values)
listAny(Employee.BasePay, Employee.Bonus)Hierarchy
Dimensions organize unique values of an attribute into a list or a hierarchical structure for use as a filter or group by in your solution. For more information, see Dimensions.
As Property
Use these functions to extract hierarchical information as properties. For example, you can retrieve the display name of a parent object.
memberAtDepth
Creates a string property that returns the display name of the ancestor of the data point's member that is at the given depth.
Examples
Get the display name of the ancestor organization at level 2 of the hierarchy.
Parameter types: (Hierarchy, Number)
memberAtDepth(Organization, 2)normalized
normalized(Hierarchy)
Creates a numeric property that calculates the normalized value of the data point's member. This calculation is only valid on numeric hierarchies with only a single level.
Examples
Get the normalized performance level. Given a minimum level of 1, a maximum level of 5, and a data point's member level of 4, the resulting value will be 0.75.
Parameter types: (Hierarchy)
normalized(PerformanceLevel)normalized(Hierarchy, Number)
Creates a numeric property that calculates the normalized value of the data point's member using a lower bound value instead of the minimum member value. This calculation is only valid on numeric hierarchies with only a single level.
Examples
Get the normalized performance level using a lower bound of 0. Given a maximum level of 5 and a data point's member level of 4, the resulting value will be 0.80.
Parameter types: (Hierarchy, Number)
normalized(PerformanceLevel, 0.0)numericDepth
Creates a numeric property that returns the minimum depth of the data point's owned org members. The depth of the hierarchy root is 0. The depth of an org head is their distance from root. The depth of an individual contributor is their org's depth plus one. This function is only supported by parent child hierarchies.
Examples
Get the lowest reporting level of an employee in the selected population.
Parameter types: (Hierarchy, String)
on Employee aggregate max(numericDepth(Supervisory_Hierarchy, "Supervisory_Head"))numericHeight
Creates a numeric property that returns the maximum height of the data point's owned org members. The height of the owner of a leaf member is 1. Where as the height of an individual contributor is 0. This function is only supported by parent child hierarchies.
Examples
Get the number of employees who are the lowest level managers of the supervisory hierarchy tree.
Parameter types: (Hierarchy, String)
on Employee filterBy(numericHeight(Supervisory_Hierarchy, "Supervisory_Head") = 1) aggregate(count(Employee.EmployeeID))ordinal
Creates a numeric property that returns the ordinal value of the data point's member. This calculation is only valid on hierarchies with only a single level.
Examples
Get the number of employee changes where performance improved.
Parameter types: (Hierarchy)
performanceLevelOrdinal := ordinal(PerformanceLevel)
on changes(Employee) occurredIn(interval) filterBy(performanceLevelOrdinal > previous(performanceLevelOrdinal)) aggregate(count(Employee.EmployeeID))parent
Creates a string property that returns the display name of the parent member of the data point's member.
Examples
Get the display name of the parent organization.
Parameter types: (Hierarchy)
parent(Organization)topLevelAncestor
Creates a string property that returns the display name of the ancestor of the data point's member that is above the level threshold. If the data point's member is already above the level threshold, then its immediate parent is returned.
Examples
Get the display name of the ancestor organization above level 3 of the hierarchy. If the employee is in an organization at level 3 or below, then the ancestor at level 2 is returned. If the employee is in an organization at level 2 or above, then the immediate parent is returned.
Parameter types: (Hierarchy, Number)
topLevelAncestor(Organization, 3)Filter
Use hierarchy filter functions to include or exclude records from your query. For example, you can exclude employees belonging to a specific department from your query results.
dynamicUserOwnedOrgs
Creates a filter for dimension members where the user is the organization head, within the selected time context. If the user is not the head of any organizations, then an empty filter is returned.
Examples
Get the dimension members where the user is the organization head, within the selected time context.
Parameter types: (Hierarchy, String)
dynamicUserOwnedOrgs(Organization, "Organization_Head")exclude
exclude(Hierarchy, String)
Creates a filter for data points that do not belong to a specific excluded member.
Examples
Get the number of employees that do not belong to the R&D organization.
Parameter types: (Hierarchy, String)
on Employee filterBy(exclude(Organization, "R&D")) aggregate(count(Employee.EmployeeID))exclude(Hierarchy, Values)
Creates a filter for data points that do not belong to any of the excluded members.
Examples
Get the number of employees that do not belong to the R&D and Product organizations.
Parameter types: (Hierarchy, Values)
on Employee filterBy(exclude(Organization, list("R&D", "Product"))) aggregate(count(Employee.EmployeeID))exclude(Hierarchy, StringListValues)
Creates a filter for data points that do not belong to any of the excluded members from a hierarchy with multiple levels.
Examples
Get the number of employees that do not reside in Vancouver and Seattle.
Parameter types: (Hierarchy, StringListValues)
on Employee filterBy(exclude(Location, list(list("Canada", "BC", "Vancouver"), list("USA", "WA", "Seattle")))) aggregate(count(Employee.EmployeeID))exclude(MemberSet)
Creates a filter for data points that do not belong to any of the excluded members.
Examples
Get the number of employees that do not belong to the R&D and Product organizations.
Parameter types: (MemberSet)
on Employee filterBy(exclude(members(Organization, list("R&D", "Product")))) aggregate(count(Employee.EmployeeID))hasValue
Creates a filter for data points that have a known hierarchy value. This does not return unknown values.
Examples
Get the number of employees that have a value for the Job Name attribute, which is a leveled dimension.
Parameter types: (Hierarchy)
on Employee filterBy(hasValue(Employee.Job_Name)) aggregate(count(Employee.EmployeeID))
Note: Specifying a hierarchy returns false for null values and values belonging to the hierarchy's "Unknown" member.
include
include(Hierarchy, String)
Creates a filter for data points that belong to a specific included member.
Examples
Get the number of employees in the R&D organization.
Parameter types: (Hierarchy, String)
on Employee filterBy(include(Organization, "R&D")) aggregate(count(Employee.EmployeeID))include(Hierarchy, Values)
Creates a filter for data points that belong to at least one of the included members.
Examples
Get the number of employees in the R&D and Product organizations.
Parameter types: (Hierarchy, Values)
on Employee filterBy(include(Organization, list("R&D", "Product"))) aggregate(count(Employee.EmployeeID))include(Hierarchy, StringListValues)
Creates a filter for data points that belong to at least one of the included members from a hierarchy with multiple levels.
Examples
Get the number of employees in Vancouver and Seattle.
Parameter types: (Hierarchy, StringListValues)
on Employee filterBy(include(Location, list(list("Canada", "BC", "Vancouver"), list("USA", "WA", "Seattle")))) aggregate(count(Employee.EmployeeID))include(MemberSet)
Creates a filter for data points that belong to at least one of the included members.
Examples
Get the number of employees in the R&D and Product organizations.
Parameter types: (MemberSet)
on Employee filterBy(include(members(Organization, list("R&D", "Product")))) aggregate(count(Employee.EmployeeID))userMember
userMember(Hierarchy)
The function returns a filter based on the organization the user belongs to, as determined by the latest hierarchy state. However, the organization is considered in its state during the selected time context.
Example:
-
If User X has been a member of Org A since January 2023 as per the latest hierarchy state, this filter returns Org A even for selected time contexts prior to January 2023.
-
If User X was a member of Org B in 2022 but is a member of Org A as of the latest hierarchy state, this filter returns Org A even if the selected time context is in 2022.
-
If User X's membership in Org A began after January 2023, but Org A didn't exist prior to January 2023, any selected time context prior to January 2023 returns an empty filter.
userMember(Hierarchy, String)
This function evaluates the organization the user is associated with based on the latest hierarchy state and operates under the following cases:
-
Case 1: If the user is an organization head AND is the organization head of the organization that they belong to based on the latest hierarchy state, then the function returns a filter based off the organization at selected time context.
-
Case 2: If the user is an organization head AND the organization they belong to is a direct parent of the organization that they head based on the latest hierarchy state, then this function returns a filter based off the organization that the user heads at the selected time context.
-
Otherwise: The function returns a filter based on the organization the user belongs to, as determined by the latest hierarchy state. However, the organization is considered in its state during the selected time context.
Example:
-
If User X has been a head of Org A since January 2023 as per the latest hierarchy state, then this filter returns Org A even for selected time contexts prior to January 2023.
-
If User X is a member of Org A but is the head of Org C, where Org A is the parent of Org C, the filter returns Org C.
-
If User X's membership in Org A began after January 2023, but Org A didn't exist prior to January 2023, any selected time context prior to January 2023 returns an empty filter.
Examples
Get the user's organization, taking into account the special case where the user is an organization head.
Parameter types: (Hierarchy, String)
userMember(Organization, "Organization_Head")userOwnedOrgs
The function returns a filter based on all the organizations the user currently heads, as determined by the latest hierarchy state. However, the filtered organizations is considered in its state during the selected time context. If the user does not head any organizations based on the latest hierarchy state, this function returns an empty filter.
Example:
-
If User X has been a head of Org A since January 2023 as per the latest hierarchy state, this filter returns Org A even for selected time contexts prior to January 2023.
-
If User X was the head of Org B from January 2019 to January 2023 and later became the organization head of Org A in January 2023, this function returns Org A even if the selected time context is January 2021.
-
If User X's membership in Org A began after January 2023, but Org A didn't exist prior to January 2023, any selected time context prior to January 2023 returns an empty filter.
Examples
Get the organizations that the user is a head of as determined by the latest hierarchy state but projected to the selected time context.
Parameter types: (Hierarchy, String)
userOwnedOrgs(Organization, "Organization_Head")userParent
Creates a filter containing parents of the user's member value. A filter represents the parents of the user's most recent hierarchy and does not account for the user's previous values of that hierarchy. If a value is not defined, the returned filter is empty.
Examples
Get the parent of the user's organization member.
Parameter types: (Hierarchy)
userParent(Organization)
Note: Accounts for the users previous values of that hierarchy.
Get the parent of the user's organization member, taking into account the special case where the user is an organization head.
Parameter types: (Hierarchy, String)
userParent(Organization, "Organization_Head")
Note: Does not account for the users previous values of that hierarchy.
userPeers
Creates a filter containing siblings of the user's member value. This filter represents the peers of the user's most recent hierarchy and does not account for the user's previous values of that hierarchy. If a value is not defined, the returned filter is empty.
Examples
Get the siblings of the user's organization member, taking into account the special case where the user is an organization head.
Parameter types: (Hierarchy, String)
userPeers(Organization, "Organization_Head")userTopLevelAncestor
Creates a filter containing top level ancestor of the user's member value. A filter that represents the top level ancestor of the user's most recent hierarchy and does not account for the user's previous values of that hierarchy. If a value is not defined, the returned filter is empty.
Examples
Get the top level ancestor of the user's organization member.
Parameter types: (Hierarchy)
userTopLevelAncestor(Organization)Get the top level ancestor of the user's organization member, taking into account the special case where the user is an organization head.
Parameter types: (Hierarchy, String)
userTopLevelAncestor(Organization, "Organization_Head")Member Set
Use hierarchy member set functions to create a subset of members that you can then use to filter your query results. For example, retrieving all members belonging to the Location Hierarchy.
allMembers
Creates a member set containing all the members of the hierarchy.
Examples
Get all the members of the location hierarchy.
Parameter types: (Hierarchy)
allMembers(Location)alphabetical
Sorts using the display name.
Examples
Get the first member when sorting VBSeries dimension members by display name in descending order.
topMember(VBSeries, alphabetical, descending)ascending
Sorts in ascending order.
Examples
Get the first member when sorting VBSeries dimension members by their ordinal in ascending order.
topMember(VBSeries, ordinal, ascending)descending
Sorts in descending order.
Examples
Get the first member when sorting VBSeries dimension members by their ordinal in descending order.
topMember(VBSeries, ordinal, descending)intersect
Creates a member set that is the intersection of two member sets.
Examples
Get the number of employees located in Canadian cities on the West Coast.
Parameter types: (MemberSet, MemberSet)
West_Coast_Cities := members(Employee.Location, List(List("North America", "Canada", "British Columbia", "Vancouver"), List("North America", "United States", "California", "Los Angeles")))
Canadian_Cities := members(Employee.Location, List(List("North America", "Canada", "British Columbia", "Vancouver"), List("North America", "Canada", "Ontario", "Toronto")))
on Employee filterBy include(intersect(West_Coast_Cities, Canadian_Cities)) aggregate count(EmployeeID)leaves
Creates a member set containing all the leaf members of the hierarchy. A leaf member is one that does not have any children.
Examples
Get all the leaf members of the location hierarchy.
Parameter types: (Hierarchy)
leaves(Location)members
members(Hierarchy, Values)
Creates a member set containing the list of members. Only members that can be found are included.
Examples
Get a member from a hierarchy that does not have a fixed level structure.
Parameter types: (Hierarchy, Values)
members(Organization, list("R&D", "Product"))Get a member from a hierarchy with a single level.
Parameter types: (Hierarchy, Values)
members(PerformanceLevel, list("Level1", "Level2"))members(Hierarchy, StringListValues)
Creates a member set containing the list of members on a hierarchy with multiple levels. Only members that can be found are included.
Examples
Get members from a hierarchy with multiple levels.
Parameter types: (Hierarchy, StringListValues)
members(Location, list(list("Canada", "BC", "Vancouver"), list("USA", "WA", "Seattle")))members(Hierarchy, String)
Creates a member set containing one specific member.
ordinal
Sort using the ordinal value.
Examples
Get the first member when sorting VBSeries dimension members by their ordinal in descending order.
topMember(VBSeries, ordinal, descending)topLevel
Creates a member set containing all the top level members of the hierarchy. A top level member is a child of the root member.
Examples
Get all the top level members of the location hierarchy.
Parameter types: (Hierarchy)
topLevel(Location)topMember
Creates a member set containing the first member from the provided dimension sorted by the settings specified.
Note:
- The top member does not include unknown members.
- The top member is selected from the members at the highest level for multi-level dimensions.
Examples
Get the first member when sorting VBSeries dimension members by their ordinal in descending order.
Parameter types: (Hierarchy, Number, Number)
topMember(VBSeries, ordinal, descending)union
Creates a member set that is the union of two member sets.
Examples
Get employees located in either Canadian or West Coast cities.
Parameter types: (MemberSet, MemberSet)
West_Coast_Cities := members(Employee.Location, List(List("North America", "Canada", "British Columbia", "Vancouver"), List("North America", "United States", "California", "Los Angeles")))
Canadian_Cities := members(Employee.Location, List(List("North America", "Canada")))
on Employee filterBy include(union(West_Coast_Cities, Canadian_Cities)) aggregate count(EmployeeID)userPeers
Creates a filter containing siblings of the user's member value. This filter represents the peers of the user's most recent hierarchy and does not account for the user's previous values of that hierarchy. If a value is not defined, the returned filter is empty.
Examples
Get the siblings of the user's organization member.
Parameter types: (Hierarchy)
userPeers(Organization)Transform
Use hierarchy transform functions to convert dimensions and change their behavior. For example, you can change the Supervisory Hierarchy to include the "Supervisor Head".
includeOrgHead
Transforms a parent child hierarchy to make it include the org head's records when evaluating the orgs that they own.
Examples
Get the possible interactions by team.
Parameter types: (Hierarchy, String)
metricValueLookup(AttendanceMetric, includeOrgHead(Direct_Manager.Supervisory_Hierarchy, "Supervisory_Head"), "Supervisory_Head")Metric
A metric is a business concern that can be quantified as a number. For more information, see Work with Metrics.
Aggregation
Use metric aggregation functions to return aggregate values, like average, sum, min, max.
average
Returns the average of the known values. In the metric settings, you can choose whether the average is returned as a percent, number, or other data type. Data points with an unknown value do not contribute to the average.
Examples
Get the average tenure at the organization.
Parameter types: (Property)
on Employee aggregate(average(Employee.Tenure))averageOverTime
Applies an aggregation function on time instants over a time interval, and returns the average of the results when using forInstantsInInterval or forIntervalsInInterval.
Examples
Get the average number of employees at monthly sampling points within the context time interval.
Parameter types: (Aggregation)
on Employee forInstantsInInterval(interval, months) aggregate(averageOverTime(count(Employee.EmployeeID)))count
Returns the number of known values. Data points with an unknown value do not contribute to the count.
Examples
Get the number of employees.
Parameter types: (Property)
on Employee aggregate(count(Employee.EmployeeID))countOverIntervalsGT
Applies a count function on time intervals over a time interval, and returns the sum on the results, excluding count values that do not exceed limit. This is used in conjunction with the time handling function forIntervalsInInterval.
Examples
Get the total physical attendance events where at least two employees were present for each day in the interval.
Parameter types: (Property, Number)
on Attendance forIntervalsInInterval(interval, day) filterBy (Attendance.isPhysical) aggregate countOverIntervalsGT(Attendance.EmployeeID, 1)distinct
Returns the number of unique values in the specified attribute. Data points with an unknown value do not contribute to the distinct count.
Note: Alternatively, you can use count with filters to achieve the same result and takes less time to run.
Examples
Get the number of managers.
Parameter types: (Property)
on Employee aggregate(distinct(Employee.ManagerID))exists
Returns if there is any data matching the filters.
Examples
Get the number of candidates that have 2 or more applications.
Parameter types: (Property, Number)
Applications := property(on Applicant via Candidate validUntil instant aggregate exists(Applicant.ApplicantID, 2))
on Candidate validUntil instant filterBy Applications > 0 aggregate count(Candidate.CandidateID)exp
Returns the exponential of the result of the given aggregation function.
Examples
Get the geometric mean of employee tenure.
Parameter types: (Aggregation)
on Employee aggregate(exp(sum(log(Employee.Tenure)) / count(Employee.EmployeeID)))log
Returns the natural logarithm of the result of the given aggregation function.
Examples
Get the geometric mean of employee tenure.
Parameter types: (Aggregation)
on Employee aggregate(exp(sum(log(Employee.Tenure)) / count(Employee.EmployeeID)))lowerQuartileRange
Returns the lower quartile of numeric values. The result may include decimal points due to interpolation when the lower quartile value falls between two data points. Data points with an unknown value do not contribute to the calculation.
Examples
Get the lower quartile employee pay level.
Parameter types: (Property)
on Employee aggregate(lowerQuartileRange(Employee.PayLevel))max
Returns the maximum known value. Data points with an unknown value do not contribute to the calculation.
Examples
Get the maximum employee tenure.
Parameter types: (Property)
on Employee aggregate(max(Employee.Tenure))median
Returns the median of numeric values. The result may include decimal points due to interpolation when the median falls between two data points. Data points with an unknown value do not contribute to the calculation.
Examples
Get the median for employee pay level.
Parameter types: (Property)
on Employee aggregate(median(Employee.PayLevel))min
Returns the minimum known value. Data points with an unknown value do not contribute to the calculation.
Examples
Get the minimum employee tenure.
Parameter types: (Property)
on Employee aggregate(min(Employee.Tenure))modeHigh
Returns the mode of the known values. When multiple values are equally frequent, this functions returns the highest of those values. Data points with an unknown value do not contribute to the calculation.
Examples
Get the mode for employee age. If multiple values occur with equal frequency, returns the highest age.
Parameter types: (Property)
on Employee aggregate(modeHigh(Employee.Age))modeLow
Returns the mode of the known values. If multiple values occur with equal frequency, return the lowest of those values. Data points with an unknown value do not contribute to the calculation.
Examples
Get the mode for employee age. If multiple values occur with equal frequency, return the lowest age.
Parameter types: (Property)
on Employee aggregate(modeLow(Employee.Age))nPercentile
Returns the nth percentile of numeric values. The function requires two parameters: the numeric property and the percentile to calculate (0-100). The result may include decimal points due to interpolation when the desired percentile falls between two data points. Data points with an unknown value do not contribute to the calculation.
Examples
Compute the 90th percentile of the ages of all employees.
Parameter types: (Property, Number)
on Employee aggregate(nPercentile(Employee.Age, 90))standardDeviation
Returns the standard deviation. Data points with an unknown value do not contribute to the calculation.
Examples
Get the standard deviation of employee pay level.
Parameter types: (Property)
on Employee aggregate(standardDeviation(Employee.PayLevel))sum
Returns the sum of the values provided. Data points with an unknown value do not contribute to the calculation.
Examples
Get the sum of compensation values.
Parameter types: (Property)
on Compensation aggregate(sum(Compensation.Amount))Get the sum of "Focus_Group_Headcount" for the plan analytic object "GuidedDiversity" based on the Guided Diversity model.
Parameter types: (Value)
on GuidedDiversity aggregate(sum(Focus_Group_Headcount))sumOverTime
Applies an aggregation function on time instants over a time interval, and returns the sum on the results when using forInstantsInInterval or forIntervalsInInterval.
Examples
Get the sum of total employees calculated monthly for the selected time interval.
Parameter types: (Aggregation)
on Employee forInstantsInInterval(interval, months) aggregate(sumOverTime(count(Employee.EmployeeID)))uniqueValue
Returns a value if all the values are the same, otherwise returns none.
Examples
Get the currency code for Compensation.CurrencyCode if they are all the same, otherwise return none.
Parameter types: (Property)
on Compensation aggregate(uniqueValue(Compensation.CurrencyCode))upperQuartileRange
Returns the upper quartile of numeric values. The result may include decimal points due to interpolation when the upper percentile value falls between two data points. Data points with an unknown value do not contribute to the calculation.
Examples
Get the upper quartile employee pay level.
Parameter types: (Property)
on Employee aggregate(upperQuartileRange(Employee.PayLevel))Syntax
Use metric syntax to construct your metric formulas.
aggregate
Keyword to add an aggregation clause to a query. Data aggregation queries must have exactly one aggregation clause.
Note: Common aggregation types include:
averageOverTime: Returns the average value over a specific time period. The integral is approximated using the trapezoidal rule on the instants specified by the time filter forInstantsInInterval. AverageOverTime can only be used with forInstantsInInterval time handling queries.
count: Returns the number of records that have a valid value in the specified attribute that the calculation is aggregating. In the above example, the formula returns the number of records on the Employee subject that have a value for the EmployeeID property.
distinct: Returns the number of unique values in the specified attribute across all records that the calculation is aggregating. This function takes longer to run than count, so you can alternatively use count with additional filters to retrieve unique values.
max: Returns the largest value of the specified attribute across all the records that the calculation is aggregating. This is useful for retrieving the latest dates for related records.
min: Returns the smallest value of the specified attribute across all the records that the calculation is aggregating. This is useful for retrieving the oldest dates for related records.
sum: Returns the total amount of the known values in the specified attribute across all records that the calculation is aggregating added together. Data points with an unknown value do not contribute to the sum.
Examples
Get the number of employees in your organization.
Parameter types: (Aggregation)
on Employee validUntil instant aggregate(count(Employee.EmployeeID))filterBy
Keyword to add an optional filter clause to a query. Use filterBy to specify the records that you want to include in the aggregation. You may use multiple filter clauses in a query. You may use existing properties, concepts, or dimensions in a filterBy clause.
Note: You can create a filter using other functions like include, exclude, hasValue, and operators like and, or, not, =, !=, <, <=, >, >=.
Examples
Get the number of high performers.
Parameter types: (Filter)
on Employee filterBy(isHighPerformer) aggregate(count(Employee.EmployeeID))lookup
Keyword to add a lookup clause to a query. This is used to find a value in a lookup overlay. Lookup overlays are pre-aggregated data sources and don't require an aggregation clause. Instead, the value can be looked up.
Examples
Look up the value of the Engagement Score on the Engagement overlay.
Parameter types: (Property)
on Engagement lookup(EngagementScore)on
Keyword to start a query on a data source. The data source is the subject, event, or overlay that the metric uses in its calculation, such as Employee or Applicant.
Examples
Get the total number of employees.
on Employee validUntil instant aggregate count(Employee.EmployeeID)
on Employee aggregate(count(Employee.EmployeeID))to
Keyword used after a via clause to specify the referenced data source. In a nested query, to specifies the outer query subject with which this nested query can be used.
Examples
Get the average number of recognitions received.
recognitionsReceived := property(on Recognition via Receiver to Employee occurredIn(periodOf(instant)) aggregate(count(Recognition.Receiver.EmployeeID)))
on Employee aggregate(average(recognitionsReceived))values
Keyword to add a list aggregation clause to the query. Use values in a nested query to retrieve a list of values from the records returned by the outer query. The data point values are collected and returned in a list.
Note: The values query is executed once using an interval and the final instant. This means that for state queries, the query checks the final state, whereas for event queries, the query checks the entire history. Because values are primarily used to filter the records in the outer query, this function may lead to unexpected results if the outer query is not operating with the same time handling configuration as the nested query.
Examples
Get the number of current employees whose last name matches the surname of an active candidate.
Parameter types: (Property)
surnames := on Candidate filterBy isActive values Last_Name
on Employee filterBy include(Last_Name, surnames) aggregate count(employeeID)via
Keyword to specify a subject reference in a nested query. Use via after the on clause to match records from the nested table with records from the outer table. via is required if the nested query and outer query are on different subjects. If via is omitted, the primary key for records on the nested table are matched with the key from outer query record. The parameter is a reference structure, which specifies the outer table and a "foreign key" column from the inner table with which to match outer query records.
Examples
Get the number of recognitions received by receiver and employee ID.
recognitionsReceived := property(on Recognition via Receiver occurredIn(periodOf(instant)) aggregate(count(Recognition.Receiver.EmployeeID)))
on Employee aggregate(average(recognitionsReceived))Time Handling
Use metric time handling functions to retrieve records that meet specific time criteria. For example, you can retrieve the number of employees with a retirement date occurring within the context time interval.
forInstantsInInterval
Creates a time filter for data points valid up until each time instant within the time interval.
Examples
Get the average number of employees at monthly sampling points within the context time interval.
Parameter types: (TimeInterval, TimePeriod)
on Employee forInstantsInInterval(interval, months) aggregate(averageOverTime(count(Employee.EmployeeID)))forIntervalsInInterval
Creates a time filter for data points that occurred within each time interval within the time interval.
Examples
Get the number of physical attendance events by day where at least two employees were present.
Parameter types: (TimeInterval, TimePeriod)
on Attendance forIntervalsInInterval(interval, day) filterBy (Attendance.isPhysical) aggregate countOverIntervalsGT(Attendance.EmployeeID, 1)lastKnownStateByFilterIn
A time filter for data points that represent the last known state of an entity in the time interval. This is only applicable for subject queries. The filter applied in the function is exclusive, meaning any record that passes the filter is treated as inactive or invalid. This function is used with the last state of a subject to find closing states within the current period.
Note: This function returns the same value as lastKnownStateIn for valid records (those that do not pass the given filter) and returns the validity start date for invalid records (those that do pass the given filter).
Examples
Get the number of applicants that were in a valid state (isActiveApplicant) before reaching an invalid state (isClosedApplicant) at some point during the selected period.
Parameter types: (Filter, TimeInterval)
on Applicant lastKnownStateByFilterIn (isClosedApplicant, interval) aggregate count(ApplicantID)lastKnownStateIn
A time filter for data points that represent the last known state of an entity in the time interval. This is only applicable for subject queries. If a member's state record is valid at the end of the interval, only that record is selected and the effective date is the endpoint of that interval. Otherwise, the last state record whose validity interval intersects the given time interval is returned and its "validityEnd" is the effective date.
Note:
- Similar to validUntil, the lastKnownStateIn function identifies valid records. It is different from validUntil because it returns records that are valid at some point in the current time interval while validUntil only returns records valid to the end of the current time interval. It is also similar to validIn in that it examines all records intersecting the given time interval, but different in that it returns only the last record found.
- LastKnownStateIn is not supported in nested queries.
Examples
Get the number of applicants whose last known state record is in the time interval.
Parameter types: (TimeInterval)
on Applicant lastKnownStateIn(interval) aggregate(count(Applicant.ApplicantID))occurredIn
occurredIn(TimeInterval)
A time filter for data points that occurred within the time interval. This is only used for event-based queries. OccurredIn precedes an interval function, such as interval, month, or periodOf. If a time filter is not specified in an event query, occurredIn(interval) is used by default.
Examples
Get the total compensation that occurred within the selected time interval.
Parameter types: (TimeInterval)
on Compensation occurredIn(interval) aggregate(sum(Compensation.Amount))occurredIn(TimeInterval, Property)
Creates a time filter for data points with a time instant property value that is within the time interval.
Examples
Get the number of employees expected to retire in the context time interval.
Parameter types: (TimeInterval, Property)
on Employee occurredIn(interval, Employee.ExpectedRetirementDate) aggregate(count(Employee.EmployeeID))validIn
A time filter for data points that are valid in the time interval. This is only applicable for subject queries and within nested queries.
Note: Unlike other state table nested queries, the effective date is not inherited from the outer query, but is obtained from an optional field in the queried table. If no date field is given, sub-nested queries are not valid.
Examples
Get the number of applicants that spent less than 10 days in the Applied stage.
Parameter types: (TimeInterval)
Total_Days_in_Applied_Stage := property(on Applicant validIn fromBeginningOfTime(instant) filterBy include(Normal_Process.Stage, "Applied") aggregate sum(daysInStateUntil(effectiveDate)))
on Applicant filterBy Total_Days_in_Applied_Stage < 10 aggregate count(ApplicantID)validUntil
A time filter for data points that are valid up until the time instant. This means validUntil returns records that are valid to the end of the selected time context. This function precedes a time instant function, such as instant, start, or shift. This is only used for subject-based queries. If a time filter is not specified in a subject query, validUntil(instant) is used by default.
Examples
Get the number of employees in the population up to the end of the selected time context.
Parameter types: (TimeInstant)
on Employee validUntil(instant) aggregate(count(Employee.EmployeeID))Transform
Use metric transform functions to return a version of your metric with additional qualifiers. For example, you can retrieve the annualized version of the employee exit rate metric.
annualize
Creates an annualized metric. This calculation is done based on the Gregorian calendar. This is not valid on a metric that is already annualized.
bestFit
Allow using values from one of the dimension member's ascendants if the dimension member has missing values
Examples
Get the best fit on the numerator of a ratio metric.
Parameter types: (Metric)
bestFit(benchmarkResignationCount) / benchmarkResignationPopulationdeannualize
Creates a de-annualized metric. This is only valid on an annualized metric.
Examples
De-annualizing the annualized employee exit rate.
Parameter types: (Metric)
deannualize(AnnualizedEmployeeExitRate)diffVsShiftedPeriod
Creates a metric that calculates the difference between this time period and previous time periods. This is only valid on a numeric metric. This calculation is based on the configured calendar.
Examples
Compare the difference in headcount between this period and last period.
Parameter types: (Metric, Number)
diffVsShiftedPeriod(Headcount, -1)else
Keyword to define a conditional statement that returns a metric to draw cell values from if the position doesn't pass the comparison check. This is used after an if clause.
Examples
Get positive growth rate values and replace non-positive growth rate values with the value 1.
if (growth_rate > 0) growth_rate else metric(1)exp
Creates a metric that calculates the exponential of a base metric.
Examples
Get the exponential growth of employee productivity proportional to the number of months in the last calendar year. This can help forecast future performance to make strategic decisions.
Parameter types: (Metric)
productivity_starting_value := shiftBack(productiveHoursRecorded, year)
productivity_ending_value := productiveHoursRecorded
employeeProductivityGrowthRate := productivity_ending_value / productivity_starting_value
exp(log(employeeProductivityGrowthRate) / monthsInCalendarYear) -1filter
Creates a filtered metric using existing metrics and attributes. Use filter to filter the population of a metric by an attribute, such as a concept or dimension. The filter must be applicable to the metric. If the metric consists of multiple underlying metrics, the filter must be applicable to all underlying metrics.
Examples
Get the number of high performers.
Parameter types: (Metric, Filter)
filter(employeeCount, IsHighPerformer)if
Keyword for starting the definition of a conditional function that returns a metric with cell values from the corresponding metrics defined inside the if else blocks. If the comparison condition is N/A then the false metric value defined in the else block will be returned. This is used in conjunction with else.
Examples
Get positive growth rate values and replace non-positive growth rate values with the value 1.
if (growth_rate > 0) growth_rate else metric(1)log
Creates a metric that calculates the natural logarithm of a base metric.
Examples
Calculate the natural logarithm of the employee's tenure. This can help in normalizing the data, especially if there are a few employees with very long tenures compared to others, which could skew analyses.
Parameter types: (Metric)
log(employeeTenureAverage)max
Creates a metric whose value will return the larger value of the two supplied metrics.
Examples
Get the larger of metric growth_rate and 0.
Parameter types: (Metric, Metric)
max(growth_rate, metric(0))metric
Converts a query into a metric. This is used in nested queries. This allows you to avoid creating a net new metric in the solution by instead writing a metric into your metric formula.
Note: The nested metric is only valid in the formula it's written in, that is, you cannot reference a nested metric in a different metric formula.
Examples
Get the average number of women in the organization.
Parameter types: (Query)
femaleHeadcount := metric(on Employee filterBy(IsFemale) aggregate(count(Employee.EmployeeID)))
headcount := metric(on Employee aggregate(count(Employee.EmployeeID)))
femaleHeadcount / headcountmetricValueLookup
The value for this property is calculated by looking at the metric value for the specified hierarchy member(s) that the record belongs to.
Examples
Get the minimum compensation for the employee's job level.
Parameter types: (Metric)
minimumPayLevel := metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel)))Get the minimum compensation for the employee's job level.
Parameter types: (Metric, Values)
minimumPayLevel := metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel), list(PayLevel))Get the minimum compensation for the employee's job level.
Parameter types: (Metric, Values, Values)
minimumPayLevel := metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel), list(PayLevel, Organization), list("", "OrgHead"))Get the minimum compensation for the employee's job level.
Parameter types: (Metric, Hierarchy)
minimumPayLevel := metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel), PayLevel)Get the minimum compensation for the employee's job level.
Parameter types: (Metric, Hierarchy, String)
minimumPayLevel := metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel), Supervisory_Hierarchy, "Supervisory_Head")min
Creates a metric whose value will return the smaller value of the two supplied metrics.
Examples
Get the smaller amount when comparing total sick leave days and total vacation days.
Parameter types: (Metric, Metric)
min(leaveTotalDaysSickLeave, leaveTotalVacationDays)mtd
Transforms a metric to look at the month up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the month-to-date.
Parameter types: (Metric)
mtd(EmployeeExitCount)orElse
Creates a metric whose value will come from the first metric argument, unless it is undefined, in which case the second metric argument is used.
Parameter types: (Metric, Metric)
predictionMetric
Creates a metric that is associated with a prediction, which enables prediction analyses on this metric. The metric must be compatible with the prediction.
Examples
Associate a risk of exit metric with a compatible risk of exit prediction.
Parameter types: (Metric, String)
predictionMetric(AverageRiskOfExit, Employee.RiskOfExit)proportion
Creates a numeric metric that calculates the proportion between two metrics.
Examples
Get the proportion of female employees.
Parameter types: (Metric, Metric)
proportion(FemaleHeadcount, Headcount)qtd
Transforms a metric to look at the quarter up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the quarter-to-date.
Parameter types: (Metric)
qtd(EmployeeExitCount)ratioVsShiftedPeriod
Creates a metric that calculates the ratio between the current value and the value from a different time. This is only valid on a numeric metric. This calculation is based on the configured calendar.
Examples
Get the headcount ratio of this period and last period.
Parameter types: (Metric, Number)
ratioVsShiftedPeriod(Headcount, -1)shift
Creates a metric calculation that fluctuates by the selected time period. This calculation is based on the configured calendar.
Examples
Get the total compensation for the time period shifted backwards in years. If the selected time period is May to August 2020, this formula would then shift the time to May to August 2019.
Parameter types: (Metric, TimePeriod)
shift(TotalCompensation, back(year))shiftBack
Creates a time shifted metric. This calculation is based on the configured calendar.
Examples
Get the total compensation a year ago.
Parameter types: (Metric, TimePeriod)
shiftBack(TotalCompensation, year)spanOfControl
Creates a metric that calculates the aggregate span of control, which is the number of subordinate objects that a superior object is responsible for. This is commonly used to calculate the number of employees reporting to a manager.
Examples
Get the average span of control for employees that are the head of an organization.
Parameter types: (Query, Hierarchy, String)
numDirectReports := property(on Employee via Manager aggregate(count(Employee.EmployeeID)))
spanOfControl(on Employee aggregate(average(numDirectReports)), Organization, "OrgHead")toDouble
Creates a metric that converts a number into a decimal number. This is used to convert non-decimal numbers into decimal numbers prior to performing arithmetic to avoid decimal truncation.
Examples
Get the proportion of headcount in double format.
Parameter types: (Metric)
toDouble(employeeCountParameterized) / toDouble(employeeCount)trailing
Transforms a metric to look at the trailing period leading up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the trailing 15 months.
Parameter types: (Metric, TimePeriod)
trailing(EmployeeExitCount, months(15))trailing12Months
Transforms a metric to look at the trailing 12 months up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the trailing 12 months.
Parameter types: (Metric)
trailing12Months(EmployeeExitCount)wtd
Transforms a metric to look at the week up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the week-to-date.
Parameter types: (Metric)
wtd(EmployeeExitCount)ytd
Transforms a metric to look at the year up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the year-to-date.
Parameter types: (Metric)
ytd(EmployeeExitCount)Operators
Use operators to perform math, comparisons, logical tests, and set variables.
!
Keyword to negate a filter. This is interchangeable with not.
Examples
Get the number of employees that became inactive during the current period.
on Employee lastKnownStateByFilterIn(!isActiveEmployee, interval) filterBy !isActiveEmployee aggregate count(employeeID)!=
Compares two values and returns true if they are not equal.
Examples
Get the number of employees that changed direct managers at least once in the last year.
on changes(Employee) occurredIn interval(year, instant) filterBy previous(Direct_ManagerID)!=Direct_ManagerID aggregate distinct(EmployeeID)%
Divides two objects and returns the remainder. This can be applied to constant values, properties, aggregations, and metrics.
Examples
Determine if an employee is having an anniversary this month by checking if the modulo of their tenure in months and 12 is 0.
monthsBetween(Employee.Start_Date, effectiveDate) % 12 = 0&&
Compares two conditions and returns true if both are true. This is interchangeable with and.
Examples
Get the number of female applicants that were interviewed by female interviewers.
femaleInterviewer := property(on Interview occurredIn fromBeginningOfTime(instant) filterBy Interviewer.isFemale aggregate count(applicantID)) > 0
on Applicant filterBy Candidate.isFemale && femaleInterviewer aggregate count(applicantID)*
Multiplies two values. This operator can be applied to constant values, properties, aggregations, and metrics.
-
Subtracts two values. This operator can be applied to constant values, properties, aggregations, and metrics.
Examples
Get the standard deviation for the difference in employee income between this year and last year.
on Employee aggregate StandardDeviation(Actual_Base_Pay_CC - ShiftBack( Actual_Base_Pay_CC, Year))/
Divides two values. This can be applied to constant values, properties, aggregations, and metrics.
Examples
Get the absence ratio for employees by dividing the number of days absent by the number of expected working days.
leaveTotalDaysAbsent / totalExpectedWorkingDays:=
Assigns intermediary calculated properties and metrics. Reduces duplication and improves readability by allowing you to create intermediary calculated properties and metrics in formulas.
Examples
Get employee bonuses using intermediary variables that assign values to "baseBonusAmount", "topTalentBonusAmount", and "managerBonusAmount".
baseBonusAmount := Employee.Salary * 0.1
topTalentBonusAmount := 2.0 * baseBonusAmount
managerBonusAmount := 1.5 * baseBonusAmount
if (IsTopTalent) topTalentBonusAmount else if (IsManager) managerBonusAmount else baseBonusAmount<
Compares two values and returns true if the one value is less than the other.
Examples
Get the number of applicants who spent less than 10 days in the Applied stage.
Total_Days_in_Applied_Stage := property(on Applicant validIn fromBeginningOfTime(instant) filterBy include(Normal_Process.Stage, "Applied") aggregate sum(daysInStateUntil(effectiveDate)))
on Applicant filterBy Total_Days_in_Applied_Stage < 10 aggregate count(ApplicantID)<=
Compares two values and returns true if the one value is less than or equal to the other.
Examples
Get the number of employees with two or fewer years of tenure at the organization.
on Employee filterBy Tenure <= 2 aggregate count(employeeID)=
Compares two values and returns true if they're equal.
Examples
Get the number of employees whose age is equal to 55.
on Employee filterBy Age = 55 aggregate count(EmployeeID)>
Compares two values and returns true if the one value is greater than the other.
Examples
Get the number of employees that conducted interviews during the period by filtering for interview counts greater than 0.
interviewCount := property(on Interview via Interviewer occurredIn periodOf(instant) aggregate count(applicantID))
on Employee filterBy interviewCount > 0 aggregate count(EmployeeID)>=
Compares two values and returns true if the one value is greater than or equal to the other.
Examples
Get the number of employees whose age is greater than or equal to 65.
on Employee filterBy Age >= 65 aggregate count(EmployeeID)and
Compares two conditions and returns true if both are true. This is interchangeable with &&.
Examples
Get the number of female applicants that were interviewed by female interviewers.
femaleInterviewer := property(on Interview occurredIn fromBeginningOfTime(instant) filterBy Interviewer.isFemale aggregate count(applicantID)) > 0
on Applicant filterBy Candidate.isFemale AND femaleInterviewer aggregate count(applicantID)not
Keyword to negate a filter. This is interchangeable with !.
Examples
Get the number of employees that became inactive during the current period.
on Employee lastKnownStateByFilterIn(not isActiveEmployee, interval) filterBy not isActiveEmployee aggregate count(employeeID)or
Compares two conditions and returns true if one condition is true. This is interchangeable with ||.
Examples
Get the number of active employees who are either male or female.
on Employee validUntil instant filterBy(isActiveEmployee && (isMale or isFemale)) aggregate count(Employee.EmployeeID)||
Compares two conditions and returns true if one condition is true. This is interchangeable with or.
Examples
Get the number of active employees who are either male or female.
on Employee validUntil instant filterBy(isActiveEmployee && (isMale || isFemale)) aggregate count(Employee.EmployeeID)Property
Properties help you to describe or add information to analytic objects. For more information, see Properties.
Filter
Use hierarchy filter functions to include or exclude records from your query. For example, you can narrow the scope of your query to only include employees who are high performers.
exclude
exclude(Property, Values)
Creates a filter for data points that have a property value not in the list of values.
Examples
Get the number of employees not managed by a list of specific employees.
Parameter types: (Property, Values)
on Employee filterBy(exclude(Employee.ManagerID, list("1001", "1002", "1003"))) aggregate(count(Employee.EmployeeID))exclude(Property, Numbers)
Creates a filter for data points that have a property value not in the list of numeric values.
Examples
Get the number of employees that have tenure that doesn't match a list of tenure values.
Parameter types: (Property, Numbers)
on Employee filterBy(exclude(Employee.Tenure, list(1, 2))) aggregate(count(Employee.EmployeeID))exclude(Property, TimeInstants)
Creates a filter for data points that have a property value not in the list of date instants.
Examples
Get the number of applicants that do not have an initial open date that matches a list of specific dates.
Parameter types: (Property, TimeInstants)
on Applicant filterBy(exclude(Applicant.InitialOpenDate, List(date("2009-06-01"),date("2009-10-01")))) aggregate(count(Applicant.ApplicantID))exclude(Property, QueryValues)
Creates a filter for data points that have a property value not in the list of values from a query result.
Examples
Get the number of employees not managed by a high performer.
Parameter types: (Property, QueryValues)
highPerformers := property(on Employee filterBy(IsHighPerformer) values(Employee.EmployeeID))
on Employee filterBy(exclude(Employee.ManagerID, highPerformers)) aggregate(count(Employee.EmployeeID))exclude(Property, String)
Creates a filter for data points that have a property value not matching a specific value.
Examples
Get the number of employees not managed by a specific employee.
Parameter types: (Property, String)
on Employee filterBy(exclude(Employee.ManagerID, "1001")) aggregate(count(Employee.EmployeeID))exclude(Property, Number)
Creates a filter for data points that have a property value not matching a specific numeric value.
Examples
Get the number of employees that have a tenure that doesn't match a specific tenure value.
Parameter types: (Property, Number)
on Employee filterBy(exclude(Employee.Tenure, 1)) aggregate(count(Employee.EmployeeID))exclude(Property, TimeInstant)
Creates a filter for data points that have a property value not matching a specific time instant.
Examples
Get the number of applicants that do not have a specific initial open date.
Parameter types: (Property, TimeInstant)
on Applicant filterBy(exclude(Applicant.InitialOpenDate, date("2009-06-01"))) aggregate(count(Applicant.ApplicantID))hasValue
A filter for data points that have a known property value. This does not return unknown values. This is different from hasValue(Hierarchy) because hasValue(Property) selects non-hierarchical attributes, like simple properties or selection concepts.
Examples
Get the number of employees that have a value for Expected Promotion Date, which is a simple property.
Parameter types: (Property)
on Employee filterBy(hasValue(Employee.ExpectedPromotionDate)) aggregate(count(Employee.EmployeeID))
Note: HasValue(Property) returns all known values, including "Unknown" and empty values, but returns false for null values.
include
include(Property, Values)
Creates a filter for data points that have a property value in the list of values.
Examples
Get the number of employees managed by an employee from a list of employees.
Parameter types: (Property, Values)
on Employee filterBy(include(Employee.ManagerID, list("1001", "1002", "1003"))) aggregate(count(Employee.EmployeeID))include(Property, Numbers)
Creates a filter for data points that have a property value in the list of numeric values.
Examples
Get the number of employees with tenure matching values from a list.
Parameter types: (Property, Numbers)
on Employee filterBy(include(Employee.Tenure, list(1, 2))) aggregate(count(Employee.EmployeeID))include(Property, TimeInstants)
Creates a filter for data points that have a property value in the list of time instants.
Examples
Get the number of applicants with initial open date matching values from a list.
Parameter types: (Property, TimeInstants)
on Applicant filterBy(include(Applicant.InitialOpenDate, List(date("2009-06-01"),date("2009-10-01")))) aggregate(count(Applicant.ApplicantID))include(Property, QueryValues)
Creates a filter for data points that have a property value in the list of values from a query result.
Examples
Get the number of employees managed by a high performer.
Parameter types: (Property, QueryValues)
highPerformers := property(on Employee filterBy(IsHighPerformer) values(Employee.EmployeeID))
on Employee filterBy(include(Employee.ManagerID, highPerformers)) aggregate(count(Employee.EmployeeID))include(Property, String)
Creates a filter for data points that have a property value matching a specific value.
Examples
Get the number of employees managed by a specific employee.
Parameter types: (Property, String)
on Employee filterBy(include(Employee.ManagerID, "1001")) aggregate(count(Employee.EmployeeID))include(Property, Number)
Creates a filter for data points that have a property value matching a specific numeric value.
Examples
Get the number of employees with tenure matching a specific value.
Parameter types: (Property, Number)
on Employee filterBy(include(Employee.Tenure, 1)) aggregate(count(Employee.EmployeeID))include(Property, TimeInstant)
Creates a filter for data points that have a property value matching a specific time instant.
Examples
Get the number of applicants with a specific initial open date.
Parameter types: (Property, TimeInstant)
on Applicant filterBy(include(Applicant.InitialOpenDate, date("2009-06-01"))) aggregate(count(Applicant.ApplicantID))Transform
Use property transform functions to derive a property from one or more properties and other inputs. For example, you can compare two numeric property values and return the higher number.
collectFirstIfAnyExists
Creates a property that picks the first property with data in a list of specified properties. All properties should be of the same type.
Examples
Get the most recent start date from Employee Start. If Employee Start has no data available, get the most recent start date from Employee instead.
Parameter types: (Values)
employment_start_date := property(on Employment_Start occurredIn fromBeginningOfTime(instant) aggregate max(effectiveDate))
collectFirstIfAnyExists(listAny(employment_start_date, Employee.Start_Date))convertCurrency
convertCurrency(Property, Property)
Creates a numeric property that performs currency conversion to the configured currency.
Note: Currency conversion data must be available in the data version to perform conversions.
Examples
Get the total compensation amount in the configured currency code.
Parameter types: (Property, Property)
on Compensation aggregate(sum(convertCurrency(Compensation.Amount, Compensation.CurrencyCode)))convertCurrency(Property, Property, String)
Creates a numeric property that performs currency conversion.
Examples
Get the total compensation amount in USD.
Parameter types: (Property, Property, String)
on Compensation aggregate(sum(convertCurrency(Compensation.Amount, Compensation.CurrencyCode, "USD")))currentStateDuration
currentStateDuration(Property)
Creates a property that calculates the time (in milliseconds) since the last change in property value. This calculation will return nothing if the current value is unknown.
Examples
Get the average number of days applicants spend in the current stage.
Parameter types: (Property)
on Applicant aggregate(average(days(currentStateDuration(Applicant.Stage))))currentStateDuration(Property, Filter)
Creates a property that calculates the time (in milliseconds) since the last change in property value, but only including the time when the data point passes the filter. This calculation will return nothing if the data point does not pass the filter or the current value is unknown.
Examples
Get the average number of days applicants spend in the current stage where the applicant is not on hold.
Parameter types: (Property, Filter)
on Applicant aggregate(average(days(currentStateDuration(Applicant.Stage, !OnHold))))currentStateStart
currentStateStart(Property)
Creates a property that calculates the time instant when the property changed to its current value. This calculation will return nothing if the current value is unknown.
Examples
Get the average number of days applicants spend in the current stage.
Parameter types: (Property)
on Applicant aggregate(average(daysBetween(currentStateStart(Applicant.Stage), effectiveDate)))currentStateStart(Property, Filter)
Creates a property that calculates the time instant when the property changed to its current value only if the data point passes the given filter. This calculation will return nothing if the data point does not pass the filter or the current value is unknown.
Examples
Get the average number of days hired applicants spend waiting to start.
Parameter types: (Property, Filter)
on Applicant aggregate(average(daysBetween(currentStateStart(Applicant.Stage, Hired && ReadyToStart), effectiveDate)))else
Keyword to define a conditional statement that returns the value of a given property if the data point does not pass any preceding filters. This is used with if.
Examples
Get the bonus an employee should receive if they are top talent, a manager, or neither.
baseBonusAmount := Employee.Salary * 0.1
topTalentBonusAmount := 2.0 * baseBonusAmount
managerBonusAmount := 1.5 * baseBonusAmount
if (IsTopTalent) topTalentBonusAmount else if (IsManager) managerBonusAmount else baseBonusAmountexp
Creates a numeric property that calculates the exponential of a number.
Examples
Get the geometric mean of employee tenure.
Parameter types: (Property)
on Employee aggregate exp(sum(Log(age))/count(EmployeeID))if
Keyword for starting the definition of a conditional function that returns the value of a property if the data point passes a given filter. This is used in conjunction with else.
Examples
Get the bonus an employee should receive if they are top talent, a manager, or neither.
baseBonusAmount := Employee.Salary * 0.1
topTalentBonusAmount := 2.0 * baseBonusAmount
managerBonusAmount := 1.5 * baseBonusAmount
if (IsTopTalent) topTalentBonusAmount else if (IsManager) managerBonusAmount else baseBonusAmountlog
Creates a numeric property that calculates the natural logarithm of a number.
Examples
Get the geometric mean of employee ages.
Parameter types: (Property)
on Employee aggregate Exp(sum(Log(age))/count(EmployeeID))max
Returns the maximum between two numbers.
Examples
Get the larger of the two when comparing the sum of sick pay and retirement and savings.
Parameter types: (Property, Property)
SickPay_PerEmployee := property(on Compensation_Payout occurredIn periodOf(instant) filterBy(Compensation_Payout.Payout_Type = "SickPay") aggregate sum(Compensation_Payout.Payout_Amount))
Savings_PerEmployee := property(on Compensation_Payout occurredIn periodOf(instant) filterBy(Compensation_Payout.Payout_Type = "Retirement and Savings") aggregate sum(Compensation_Payout.Converted_Pay_Amount))
max(SickPay_PerEmployee, Savings_PerEmployee)min
Returns the minimum between two numbers.
Examples
Get the smaller of the two when comparing the sum of sick pay and retirement and savings.
Parameter types: (Property, Property)
SickPay_PerEmployee := property(on Compensation_Payout occurredIn periodOf(instant) filterBy(Compensation_Payout.Payout_Type = "SickPay") aggregate sum(Compensation_Payout.Payout_Amount))
Savings_PerEmployee := property(on Compensation_Payout occurredIn periodOf(instant) filterBy(Compensation_Payout.Payout_Type = "Retirement and Savings") aggregate sum(Compensation_Payout.Converted_Pay_Amount))
min(SickPay_PerEmployee, Savings_PerEmployee)noValue
Creates an empty property based on the specified property type.
Examples
If an employee has no direct reports, return no value for the employee. Otherwise, return the number of direct reports.
Parameter types: (Property)
if (Employee.Span_Of_Control = 0)
noValue(Employee.Span_Of_Control)
else
Employee.Span_Of_Controlproperty
Converts a query into a calculated property. This is used in nested queries. This allows you to avoid creating net new properties in the solution by writing a property into your metric formula.
Note: The nested property is only valid in the formula it's written in, that is, you cannot reference a nested property in a different metric formula.
Examples
Get the average compensation amount for employees in the organization.
Parameter types: (Query)
compensationAmount := property(on Compensation occurredIn(interval) aggregate(sum(Compensation.Amount)))
on Employee aggregate(average(compensationAmount))sigmoid
Creates a numeric property that applies the standard logistic function on a numeric value, producing a value between 0 and 1 along a smooth S-curve. Large positive values are mapped closer to 1. Large negative values are mapped closer to 0.
Examples
Get the standardized employee age property, with an age of 20 being the 5% value, an age of 30 being the 50% value, and an age of 60 being the 95% value.
Parameter types: (Property, Property, Property)
sigmoid(Employee.Age, 20, 60)Get the standardized employee age property, with an age of 20 being the 5% value, and an age of 60 being the 95% value.
Parameter types: (Property, Property, Property, Property)
sigmoid(Employee.Age, 20, 30, 60)sumIfAnyExists
Creates a numeric property that calculates the sum of values, ignoring any unknown or non-existent values.
Examples
Get the average employee base pay and bonus, where bonus may not exist for some employees.
Parameter types: (Values)
on Employee aggregate(average(sumIfAnyExists(listAny(Employee.BasePay, Employee.Bonus))))toDoubleAttribute
Explicitly converts a decimal number into a decimal property. This is usually not necessary as the conversion is done implicitly.
Parameter types: (Number)
toDoubleProperty
Creates a numeric property that converts a number into a decimal number. This is used to convert non-decimal numbers into decimal numbers prior to performing arithmetic to avoid decimal truncation.
Parameter types: (Property)
Time
Time formulas are helpful when working with date and time attributes. For example, you can shift a date forward or backward by a number of days, weeks, months, or years.
attribute
Converts a time instant into a time instant property. This is usually not necessary as the conversion is done implicitly.
Examples
Get the number of days between an employee's start date and the selected time instant.
Parameter types: (TimeInstant)
daysBetween(Employee.StartDate, attribute(instant))back
Shift the time instance forward or backward for a time period.
Note: You can use a negative value to go forward in time.
combine
Adds two time periods together.
Examples
Create a period of 1 year and 6 months.
Parameter types: (TimePeriod, TimePeriod)
combine(year, months(6))date
Returns a time instant described by the input string. This string input must comply with ISO 8601.
dayOfWeek
dayOfWeek(Property, Property)
Creates a numeric property that returns which day of the week a time instant property falls on, given the time zone. This calculation is based on the configured calendar.
Examples
Get the day of the week where an employee card swipe event takes place in their local time zone, based on the configured calendar.
Parameter types: (Property, Property)
dayOfWeek(CardSwipe.EventDate, CardSwipe.TimeZone)dayOfWeek(Property, Property, Number)
Creates a numeric property that returns which day of the week a time instant property falls on, given the time zone.
Examples
Get the day of the week where an employee card swipe event takes place in their local time zone, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
dayOfWeek(CardSwipe.EventDate, CardSwipe.TimeZone, gregorian)days
days(Number)
Creates a time period of a given number of days.
Examples
Shift the selected time instant forward by 365 days.
Parameter types: (Number)
shift(instant, days(365))days(Property)
Creates a numeric property that convert milliseconds into days as a decimal number.
Examples
Get the average of applicants in days spent in the current stage.
Parameter types: (Property)
on Applicant aggregate(average(days(currentStateDuration(Applicant.Stage))))daysBetween
daysBetween(Property, Property)
Creates a numeric property that calculates the number of days between two time instants. This calculation is based on the configured calendar. The earlier date should come first in the formula.
Note: If the start date is in the middle of a day and end date is in the middle of another day, and the two incomplete days form a full day, daysBetween counts that as one day.
Examples
Get the number of days between an employee's start date and effective date, based on the configured calendar.
Parameter types: (Property, Property)
daysBetween(Employee.StartDate, effectiveDate)Get the number of days between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property)
daysBetween(Employee.StartDate, effectiveDate, gregorian)daysBetween(Property, Property, Number)
Creates a numeric property that calculates the number of days between two time instants. The earlier date should come first in the formula.
Note: If the start date is in the middle of a day and end date is in the middle of another day, and the two incomplete days form a full day, daysBetween counts that as one day.
Examples
Get the number of days between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
daysBetween(Employee.StartDate, effectiveDate, gregorian)earlier
Returns the earlier of two time instants.
Examples
Get the number of employees as of the current instant or as of January 1 of 2016, whichever comes first.
Parameter types: (TimeInstant, TimeInstant)
on Employee validUntil earlier(instant, date("2016-01-01")) aggregate count(EmployeeID)earliest
Returns the earliest date for which data is available.
Examples
Get the total compensation for compensation events that occurred across all time.
on Compensation occurredIn(interval(earliest, instant)) aggregate(sum(Compensation.Amount))effectiveDate
Returns the time instant at which the data point is observed. Use effectiveDate to specify the time at which the query's records should be calculated.
Note:
- If no time filter is specified in a nested query, either validUntil(effectiveDate) or "validAt(effectiveDate)" are used in the nested query, dependent on the outer query.
- For an event record, effectiveDate comes from the event date. Normally, the instant of the event is the start of day, including PriorState event tables and those based on ending event tables, however, certain ending event tables give effectiveDate as the end of day (or, start of day on the next day).
- For a state record in a nested query, effectiveDate is inherited from the outer query record, except for the validIn time filter with a "dateField".
-
For a state record in an outer query, the way effectiveDate functions depends on the time filter:
-
ValidUntil gives the "timeInstant" parameter from the time filter.
-
ForInstantsInInterval gives the instant in the interval for which the record has been selected. The same record may be used at different instants with different effective dates.
-
LastKnownStateIn gives the earlier of either the "validityEnd" from the record or the end of the "timeInterval" parameter.
-
LastKnownStateByFilterIn returns the same as lastKnownStateIn above for "Open" records (those that do not pass the given filter), and returns the "validityStart" date for "Closed" records (those that do pass the given filter).
-
OccurredIn(TimeInterval, DateAttribute) always gives latest, the end-of-data time, as it returns information about the future based on the last data available.
-
Caution: Because effectiveDate is inherited from the outer query, further nested queries on a nested query state record will not relate to the validity interval of that nested record, but will return results relevant to the outer query date. However, sub-nested queries are not valid for validIn queries without an explicit "dateField".
Tip: For time-sensitive calculations such as Age, outside of time filters, effectiveDate provides the current time for the current record to use in calculations. This is not available inside time filters, where instant gives the current time from the display time axis.
Examples
Get the average number of days between the employee's last promotion and the effective date using the Gregorian calendar.
on Employee aggregate(average(daysBetween(Employee.LastPromotionDate, effectiveDate, gregorian)))end
Returns the end of a time interval.
Examples
Get the number of days between the selected period. If the selected period is January, then the calculation returns 31 days.
Parameter types: (TimeInterval)
daysBetween(start(interval), end(interval))expectedTotalWorkingHours
Creates a metric that calculates the total expected working hours of a full-time employee within the context time interval.
Note: This calculation is based on the configured calendar. If the expected working hours per day is not defined, then 7.5 is used.
Examples
Get the average compensation per FTE.
totalCompensation := metric(on Compensation_Payout occurredIn interval filterBy(isTCOWActualBasePay) aggregate sum(convertCurrency(Compensation_Payout.Payout_Amount, Compensation_Payout.Pay_Currency_Code)))
totalHoursWorked := metric(on Compensation_Payout occurredIn interval aggregate sum(Compensation_Payout.Payout_Hours))
fte := totalHoursWorked / expectedTotalWorkingHours
totalCompensation / fteextend
extend(TimeInterval, TimePeriod)
Creates an extended time interval from a time interval and a time period. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the context time interval and 2 months after.
Parameter types: (TimeInterval, TimePeriod)
on Compensation occurredIn(extend(interval, months(2))) aggregate(sum(Compensation.Amount))extend(TimeInterval, TimeInterval)
Creates an extended time interval from two time intervals. The resulting interval starts from the start of the first time interval, and ends at the end of the second time interval.
Examples
Get the number of exits that occurred in the last two periods.
Parameter types: (TimeInterval, TimeInterval)
on Employee_Exit occurredIn extend(interval, back(period)) aggregate count(Employee_Exit.EmployeeID)fromBeginningOfTime
Creates a time interval from the earliest date for which data is available.
Examples
Get the total compensation for all compensation events that occurred from the start of data and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(fromBeginningOfTime(instant)) aggregate(sum(Compensation.Amount))gregorian
Use the Gregorian calendar, as opposed to the configured calendar.
Examples
Get the average number of months since employees were last promoted, using the Gregorian calendar.
on Employee aggregate(average(monthsBetween(Employee.LastPromotionDate, effectiveDate, gregorian)))instant
A time filter that specifies the current point in time according to the time context.
Note:
- For outer queries, this corresponds to the end of the selected time period.
- For nested queries, this corresponds to the effective date of the parent data point in the outer query.
Examples
Get the total number of employees in the population up until the end of the selected time context.
on Employee validUntil(instant) aggregate(count(Employee.EmployeeID))interval
interval()
A time filter that specifies the context time interval. This corresponds to the selected time interval. This is only valid when used within an outer query, and is not valid when used within a nested query.
Examples
Get the total compensation for a specific time period.
on Compensation occurredIn(interval) aggregate(sum(Compensation.Amount))interval(TimeInstant, TimePeriod)
Creates a time interval starting from the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the 12 months leading up to the context time instant.
Parameter types: (TimeInstant, TimePeriod)
on Compensation occurredIn(interval(instant, back(months(12)))) aggregate(sum(Compensation.Amount))interval(TimePeriod, TimeInstant)
Creates a time interval ending at the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the 12 months leading up to the context time instant.
Parameter types: (TimePeriod, TimeInstant)
on Compensation occurredIn(interval(months(12), instant)) aggregate(sum(Compensation.Amount))interval(String, String)
Creates a time interval starting from a time instant, and ending at another time instant.
Examples
Get the total compensation for all compensation events that occurred between two arbitrary time instants.
Parameter types: (String, String)
on Compensation occurredIn(interval("2010-01-01", "2011-01-01")) aggregate(sum(Compensation.Amount))interval(TimeInstant, TimeInstant)
Creates a time interval starting from a time instant, and ending at another time instant.
Examples
Get the total compensation for all compensation events up to the context time instant.
Parameter types: (TimeInstant, TimeInstant)
on Compensation occurredIn(interval(earliest, instant)) aggregate(sum(Compensation.Amount))later
Returns the later of two time instants.
Examples
Get the number of employees as of the current instant or as of January 1 of 2016, whichever comes later.
Parameter types: (TimeInstant, TimeInstant)
on Employee validUntil later(instant, date("2016-01-01")) aggregate count(EmployeeID)latest
Returns the latest date for which data is available.
Examples
Get the total compensation for compensation events that occurred across all time.
on Compensation occurredIn(interval(earliest, latest)) aggregate(sum(Compensation.Amount))millisBetween
millisBetween(Property, Property)
Calculates the number of milliseconds between two time instants. This calculation is based on the configured calendar.
Examples
Get the number of milliseconds between an employee's start date and effective date.
Parameter types: (Property, Property)
millisBetween(Employee.StartDate, effectiveDate)millisBetween(Property, Property, Number)
Creates a numeric property that calculates the number of milliseconds between two time instants.
Examples
Get the milliseconds between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
millisBetween(Employee.StartDate, effectiveDate, gregorian)millisOfDay
Creates a property that returns the milliseconds from the start of day of a time instant property, given the time zone.
Examples
Get the milliseconds of the day that an employee card swipe event takes place in their local time zone.
Parameter types: (Property, Property)
millisOfDay(CardSwipe.EventDate, CardSwipe.TimeZone)month
month()
Creates a time period of 1 month.
month(TimeInstant)
Creates a time interval for the month of the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the month of the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(month(instant)) aggregate(sum(Compensation.Amount))months
Returns a time period of a given number of months.
Examples
Shift the context time instant forward by 12 months.
Parameter types: (Number)
shift(instant, months(12))monthsBetween
monthsBetween(Property, Property)
Creates a numeric property that calculates the number of months between two time instants. This calculation is based on the configured calendar.
Examples
Get the number of months between an employee's start date and effective date, based on the configured calendar.
Parameter types: (Property, Property)
monthsBetween(Employee.StartDate, effectiveDate)monthsBetween(Property, Property, Number)
Creates a numeric property that calculates the number of months between two time instants.
Examples
Get the number of months between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
monthsBetween(Employee.StartDate, effectiveDate, gregorian)monthsInCalendarYear
Returns the number of months in a calendar year, based on the configured calendar.
Examples
Get the average monthly total compensation amount over the last 12 months.
on Compensation occurredIn(trailing12Months(instant)) aggregate(sum(Compensation.Amount) / monthsInCalendarYear)mtd
Returns a time interval from the beginning of the month and up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the month and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(mtd(instant)) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the month and up to the context time instant.
on Compensation occurredIn(mtd) aggregate(sum(Compensation.Amount))period
Returns the time context. This corresponds to the time period a user selects in a visualization.
Note: The period value is always a positive integer representing the time period, followed by the time axis value, such as month or quarter. For example, a period query could use the value "3 years".
Examples
Shift the selected time in the visualization backward or forward by the selected time period from the current instant.
shift(instant, period)periodOf
Creates a time interval for the time period context of the time instant. This calculation is based on the configured calendar. The time period is user-selected and can be a week, month, quarter, year, or a time range.
Note: If the instant is the same as the beginning of the interval, periodOf adjusts the beginning of the interval to the following time period.
Examples
Get the total compensation amount that occurred during the time period for the current instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(periodOf(instant)) aggregate(sum(Compensation.Amount))periodToDate
periodToDate(TimePeriod, TimeInstant)
Creates a time interval from the beginning of the time period to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
Parameter types: (TimePeriod, TimeInstant)
on Compensation occurredIn(periodToDate(period, instant)) aggregate(sum(Compensation.Amount))periodToDate(TimeInstant, TimePeriod)
Creates a time interval from the beginning of the time period to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
Parameter types: (TimeInstant, TimePeriod)
on Compensation occurredIn(periodToDate(instant, period)) aggregate(sum(Compensation.Amount))periodToDate(TimePeriod)
Creates a time interval from the beginning of the time period to the context time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
Parameter types: (TimePeriod)
on Compensation occurredIn(periodToDate(period)) aggregate(sum(Compensation.Amount))periodToDate(TimeInstant)
Creates a time interval from the beginning of the context time period to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(periodToDate(instant)) aggregate(sum(Compensation.Amount))periodToDate()
Creates a time interval from the beginning of the context time period to the context time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
on Compensation occurredIn(periodToDate) aggregate(sum(Compensation.Amount))qtd
Creates a time interval from the beginning of the quarter and up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the quarter and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(qtd(instant)) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the quarter and up to the context time instant.
on Compensation occurredIn(qtd) aggregate(sum(Compensation.Amount))quarter
quarter()
Returns a time period of 1 quarter.
quarter(TimeInstant)
Creates a time interval for the quarter of the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the quarter of the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(quarter(instant)) aggregate(sum(Compensation.Amount))quarters
Creates a time period of a given number of quarters.
Examples
Shift the context time instant forward by 4 quarters.
Parameter types: (Number)
shift(instant, quarters(4))shift
shift(TimeInstant, TimePeriod)
Shifts a time instant by a time period. This calculation is based on the configured calendar.
shift(TimeInterval, TimePeriod)
Creates a time interval calculation that fluctuates by the selected time period. This calculation is based on the configured calendar.
Examples
Get the total compensation that occurred during the period, shifted backwards in time by 12 months. If the selected time period is May to August 2020, this formula would then shift the time to May to August 2019.
Parameter types: (TimeInterval, TimePeriod)
on Compensation occurredIn(shift(interval, back(months(12)))) aggregate(sum(Compensation.Amount))shift(Property, TimePeriod)
Creates a property calculation that fluctuates by the selected time period. This calculation is based on the configured calendar.
Examples
Get the number of employees who started at the organization for the time period shifted backwards in years. If the selected time period is May to August 2020, this formula would then shift the time to May to August 2019.
Parameter types: (Property, TimePeriod)
on Employee filterBy(effectiveDate < shift(Employee.StartDate, year)) aggregate(count(Employee.EmployeeID))shift(Property, TimePeriod, Number)
Creates a time instant property that shifts a time instant by a time period.
Examples
Get the number of employees who started less than a year ago, based on the Gregorian calendar.
Parameter types: (Property, TimePeriod, Number)
on Employee filterBy(effectiveDate < shift(Employee.StartDate, year, gregorian)) aggregate(count(Employee.EmployeeID))shiftBack
Creates a time instant property that shifts a time instant back by a time period. This calculation is based on the configured calendar.
Examples
Get the number of employees who started less than a year ago.
Parameter types: (Property, TimePeriod)
on Employee filterBy(Employee.StartDate > shiftBack(effectiveDate, year)) aggregate(count(Employee.EmployeeID))start
Returns the start of a time interval.
Examples
Get the number of employees that are valid at the start of the interval.
Parameter types: (TimeInterval)
on Employee validUntil(start(interval)) aggregate(count(Employee.EmployeeID))trailing
trailing(TimeInstant, TimePeriod)
Creates a time interval from the beginning of the trailing period leading up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the last 15 months leading up to the context time instant.
Parameter types: (TimeInstant, TimePeriod)
on Compensation occurredIn(trailing(instant, months(15))) aggregate(sum(Compensation.Amount))trailing(TimePeriod)
Creates a time interval from the beginning of the trailing period leading up to the context time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the last 15 months leading up to the context time instant.
Parameter types: (TimePeriod)
on Compensation occurredIn(trailing(months(15))) aggregate(sum(Compensation.Amount))trailing12Months
trailing12Months(TimeInstant)
Creates a time interval of the trailing 12 months leading up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the last 12 months leading up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(trailing12Months(instant)) aggregate(sum(Compensation.Amount))trailing12Months()
Creates a time interval of the trailing 12 months leading up to the context time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the last 12 months leading up to the context time instant.
on Compensation occurredIn(trailing12Months) aggregate(sum(Compensation.Amount))week
week()
Returns a time period of 1 week.
week(TimeInstant)
Creates a time interval for the week of the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the week of the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(week(instant)) aggregate(sum(Compensation.Amount))weeks
Creates a time period of a given number of weeks.
Examples
Shift the context time instant forward by 52 weeks.
Parameter types: (Number)
shift(instant, weeks(52))workingDaysBetween
workingDaysBetween(Property, Property)
Creates a numeric property that calculates the number of working days between two time instants. If the configured calendar is a Gregorian calendar, it will only consider week days. However, if it's a custom calendar, it will consider any day where its fullTimeHours is greater than zero.
Examples
Get the number of working days between an employee's start date and effective date, based on the configured calendar.
Parameter types: (Property, Property)
workingDaysBetween(Employee.StartDate, effectiveDate)workingDaysBetween(Property, Property, Number)
Creates a numeric property that calculates the number of working days between two time instants. If this is used with Gregorian calendar, it will only consider week days. However, if it's used with custom calendar, it will consider any day where its fullTimeHours is greater than zero.
Examples
Get the number of working days between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
workingDaysBetween(Employee.StartDate, effectiveDate, gregorian)wtd
wtd(TimeInstant)
Creates a time interval from the beginning of the week and up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the week and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(wtd(instant)) aggregate(sum(Compensation.Amount))wtd()
Creates a time interval from the beginning of the week and up to the context time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the week and up to the context time instant.
on Compensation occurredIn(wtd) aggregate(sum(Compensation.Amount))year
year()
Returns a time period of one year.
Examples
Get the average number of employees for the previous year in monthly counts.
on Employee forInstantsInInterval(interval(instant,back(year)), Month) aggregate averageOverTime(count(EmployeeID))year(TimeInstant)
Creates a time interval for the year of the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the year of the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(year(instant)) aggregate(sum(Compensation.Amount))years
Creates a time period of a given number of years.
Examples
Shift the context time instant forward by 1 year.
Parameter types: (Number)
shift(instant, years(1))yearsBetween
yearsBetween(Property, Property)
Creates a numeric property that calculates the number of years between two time instants. This calculation is based on the configured calendar.
Examples
Get the number of years between an employee's start date and effective date.
Parameter types: (Property, Property)
yearsBetween(Employee.StartDate, effectiveDate)yearsBetween(Property, Property, Number)
Creates a numeric property that calculates the number of years between two time instants.
Examples
Get the number of years between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
yearsBetween(Employee.StartDate, effectiveDate, gregorian)ytd
ytd(TimeInstant)
Creates a time interval from the beginning of the year to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the year and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(ytd(instant)) aggregate(sum(Compensation.Amount))ytd()
Creates a time interval from the beginning of the year and up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the year and up to the context time instant.
on Compensation occurredIn(ytd) aggregate(sum(Compensation.Amount))