Visier Formula Language (VFL)

Use the Visier Formula Language (VFL) to define metrics in the Visier platform.

This is a complete list of 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.

Metric

A metric is a business concern that can be quantified as a number. For more information, see Work with Metrics.

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: For common aggregation types, see Aggregation.


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, ornot, =, !=, <, <=, >, >=.


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.


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.


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.


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


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.

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.


forIntervalsInInterval

Creates a time filter for data points that occurred within each time interval within the time interval.


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


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.


occurredIn

Creates a time filter for data points with a time instant property value that is within the time interval.


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.


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.

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.


averageOverTime

Applies an aggregation function on time instants over a time interval, and returns the average of the results when using forInstantsInInterval or forIntervalsInInterval.


count

Returns the number of known values. Data points with an unknown value do not contribute to the count.


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.


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.


exists

Returns if there is any data matching the filters.


exp

Returns the exponential of the result of the given aggregation function.


log

Returns the natural logarithm of the result of the given aggregation function.


lowerQuartileRange

Returns the lower quartile of the known values. Data points with an unknown value do not contribute to the calculation.


max

Returns the maximum known value. Data points with an unknown value do not contribute to the calculation.


median

Returns the median of the known values. Data points with an unknown value do not contribute to the calculation.


min

Returns the minimum known value. Data points with an unknown value do not contribute to the calculation.


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.


modeLow

Returns the mode of the known values. When multiple values are equally frequent, this functions returns the lowest of those values. Data points with an unknown value do not contribute to the calculation.


nPercentile

Returns the nth percentile of numeric values. 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.


standardDeviation

Returns the standard deviation. Data points with an unknown value do not contribute to the calculation.


sum

Returns the sum of the values provided. Data points with an unknown value do not contribute to the calculation.


sumOverTime

Applies an aggregation function on time instants over a time interval, and returns the sum on the results when using forInstantsInInterval or forIntervalsInInterval.


uniqueValue

Returns a value if all the values are the same, otherwise returns none.


upperQuartileRange

Returns the upper quartile of the known values. Data points with an unknown value do not contribute to the calculation.

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.


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.


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.


exp

Creates a metric that calculates the exponential of a base metric.

Parameter types: (Metric)


filter

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.


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.


log

Creates a metric that calculates the natural logarithm of a base metric.

Parameter types: (Metric)


max

Creates a metric whose value will return the larger value of the two supplied metrics.


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.


metricValueLookup

The value for this property is calculated by looking at the metric value for the specified hierarchy member(s) that the record belongs to.


min

Creates a metric whose value will return the smaller value of the two supplied metrics.


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.


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.


proportion

Creates a numeric metric that calculates the proportion between two metrics.


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.


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.


shift

Creates a metric calculation that fluctuates by the selected time period. This calculation is based on the configured calendar.


shiftBack

Creates a time shifted metric. This calculation is based on the configured calendar.


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.


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.


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.


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.


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.


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.

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.

Subject State Changes

Use analytic object subject state changes functions 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.


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.


hasChanged

Creates a filter for changes where the value for a hierarchy changed.


nextChanges

Creates an event view on a subject that returns each change-to event.


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 using with any change views that do not include change-from events, or trying to use previous as the parameter value (eg, previous(previous(filter)).


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.


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 events occur when the end state of a subject member does not have a subsequent state.
  • Termination event date is the same as validityEnd date.


Subject State Traversals

Use analytic object subject state traversals functions 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.


from

Specifies the start condition for traversals, and typically pairs with to which specifies the end condition.


starting

Creates a property that calculates on the starting state of a traversal, as opposed to the ending state of a traversal.


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.


to

Specifies the end condition for traversals, and typically pairs with from which specifies the start condition.


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.


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.


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.

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.


normalized

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.


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.


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.


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.


parent

Creates a string property that returns the display name of the parent member of the data point's member.


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.

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.

exclude

Excludes records that do belong to specific hierarchy members.


hasValue

Creates a filter for data points that have a known value. This does not return unknown values.


include

Includes records that belong to specific hierarchy members.


userMember

Creates a filter containing the user's member. The user's member is based on the user's configuration. If this is not defined, then an empty filter is returned.


userOwnedOrgs

Creates a filter containing the hierarchy members that the user is the organization head of. If the user is head of no organizations, then an empty filter is returned.


userParent

Creates a filter containing parents of the user's member value. A filter represents the parents of the user's most recent hierarchy. If a value is not defined, the returned filter is empty.


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.


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 hierarchy members.


alphabetical

Sorts using the display name.


ascending

Sorts in ascending order.


descending

Sorts in descending order.


intersect

Creates a member set that is the intersection of two member sets.


leaves

Creates a member set containing all the leaf members of the hierarchy. A leaf member is one that does not have any children.


members

Creates a member set containing the list of members. Only members that can be found are included.


ordinal

Sort using the ordinal value.


root

Creates a member set containing the root member of the hierarchy.


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.


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.


union

Creates a member set that is the union of two member sets.


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.

Transform

Use hierarchy transform functions to convert dimensions and change it's behavior. For example, you can change the supervisor 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.

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 that are high performers.

exclude

Excludes records that do not have specific property values.


hasValue

Creates a filter for data points that have a known value. This does not return unknown values.


include

Includes records that have specific property values.

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.


convertCurrency

Create a numeric property that performs currency conversion. The currency is converted given a string attribute holding the source currency code and an implicit time value. The implicit time instant comes from the query end date and is used to look up the conversion rate at that instant.

Note: Currency conversion data must be available in the data version to perform conversions.


currentStateDuration

Creates a property that calculates the time (in milliseconds) since the last change in property value.


currentStateStart

Creates a property that calculates the time instant when the property changed to its current value.


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.


exp

Creates a numeric property that calculates the exponential of a number.


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.


log

Creates a numeric property that calculates the natural logarithm of a number.


max

Returns the maximum between two numbers.


min

Returns the minimum between two numbers.


noValue

Creates an empty property based on the specified property type.


property

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.


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.


sumIfAnyExists

Creates a numeric property that calculates the sum of values, ignoring any unknown or non-existent values.


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


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.


date

Returns a time instant described by the input string. This string input must comply with ISO 8601.


day

Creates a time period of 1 day.


dayOfWeek

Creates a numeric property that returns which day of the week a time instant property falls on, given the time zone.


days

Creates a time period of a given number of days.


days

Creates a numeric property that convert milliseconds into days as a decimal number.


daysBetween

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.


earlier

Returns the earlier of two time instants.


earliest

Returns the earliest date for which data is available.


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:

  • 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 always gives latest, the end-of-data time, as it returns information about the future based on the last data available.

  • If no time filter is specified in a nested query, either validUntil or "validAt(effectiveDate)" are used in the nested query, dependent on the outer query.

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.


end

Returns the end of a time 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.


extend

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.


fromBeginningOfTime

Creates a time interval from the earliest date for which data is available.


gregorian

Use the Gregorian calendar, as opposed to the configured calendar.


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.


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.


interval

Creates a time interval starting from a time instant, and ending at another time instant.


later

Returns the later of two time instants.


latest

Returns the latest date for which data is available.


millisBetween

Creates a numeric property that calculates the number of milliseconds between two time instants.


millisOfDay

Creates a property that returns the milliseconds from the start of day of a time instant property, given the time zone.


month

Returns a time period of 1 month.


month

Creates a time interval for the month of the time instant. This calculation is based on the configured calendar.


months

Returns a time period of a given number of months.


monthsBetween

Creates a numeric property that calculates the number of months between two time instants.


monthsInCalendarYear

Returns the number of months in a calendar year, based on the configured calendar.


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.


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


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.


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.


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.


quarter

Returns a time period of 1 quarter.


quarter

Creates a time interval for the quarter of the time instant. This calculation is based on the configured calendar.


quarters

Creates a time period of a given number of quarters.


shift

Creates a time instant property that shifts a time instant by a time period.


shiftBack

Creates a time instant property that shifts a time instant back by a time period. This calculation is based on the configured calendar.


start

Returns the start of a time interval.


trailing

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.


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.


week

Creates a time period of 1 week.


week

Creates a time interval for the week of the time instant. This calculation is based on the configured calendar.


weeks

Creates a time period of a given number of weeks.


workingDaysBetween

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.


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.


year

Creates a time period of one year.


year

Creates a time interval for the year of the time instant. This calculation is based on the configured calendar.


years

Creates a time period of a given number of years.


yearsBetween

Creates a numeric property that calculates the number of years between two time instants.


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.


Collections

Use collections 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.


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.

Operators

Use operators to perform math, comparisons, logical tests, and set variables.

-

Subtracts two values. This operator can be applied to constant values, properties, aggregations, and metrics.


:=

Assigns intermediary calculated properties and metrics. Reduces duplication and improves readability by allowing you to create intermediary calculated properties and metrics in formulas.


!

Keyword to negate a filter. This is interchangeable with not.


!=

Compares two values and returns true if they are not equal.


*

Multiplies two values. This operator can be applied to constant values, properties, aggregations, and metrics.


/

Divides two values. This can be applied to constant values, properties, aggregations, and metrics.


&&

Compares two conditions and returns true if both are true. This is interchangeable with and.


%

Divides two objects and returns the remainder. This can be applied to constant values, properties, aggregations, and metrics.


+

Adds two values. This can be applied to constant values, properties, aggregations, and metrics.


<

Compares two values and returns true if the one value is less than the other.


<=

Compares two values and returns true if the one value is less than or equal to the other.


=

Compares two values and returns true if they're equal.


>

Compares two values and returns true if the one value is greater than the other.


>=

Compares two values and returns true if the one value is greater than or equal to the other.


||

Compares two conditions and returns true if one condition is true. This is interchangeable with or.


and

Compares two conditions and returns true if both conditions are true. This is interchangeable with &&.


not

Compares two values and returns true if they are not equal. This is interchangeable with !.


or

Compares two conditions and returns true if one condition is true. This is interchangeable with ||.