A quick-reference glossary of the terms used in the Formula Assistant when building searches and formulas in Insights & Analytics.Use this glossary as a fast lookup for formula terminology. For full descriptions and examples of each function, see the Formula function reference.
Key concepts
Measure: a numeric, quantitative column that can be aggregated (summed, averaged, counted, and so on) — for example, revenue or call duration. Attribute: a descriptive, categorical column used to group, filter, or label data — for example, region or category.Function categories at a glance
Selected definitions
average: Returns the average of all the values of a column. Example: average (revenue)
count: Returns the number of rows in the table containing the column. Example: count (product)
group_aggregate: Takes a Measure and optional attributes and filters. Used to aggregate Measures with different granularities and filters than the columns used in the search. Commonly used in comparison analysis. Example: group_aggregate (<aggregation(Measure)>, <groupings>, <filters>)
moving_average: Takes a Measure, two integers to define the window to aggregate over, and one or more attributes. Returns the average of the Measure over the given window. Example: moving_average (revenue, 2, 1, customer region)
rank: Returns the rank for the current row. Identical values receive an identical rank. Takes an aggregate input for the first argument; the second specifies order, 'asc' | 'desc'. Example: rank (sum (revenue), 'asc')
to_string: Returns the input as a text string. To convert a date data type to a string data type, specify the date format you want to use in the second argument (for example, to_string ( <date column>, "%Y-%m-%d" )). Use strftime for the date format. Example: to_string (order_date, "%Y-%m-%d")
now: Returns the current date and time in your locale’s standard date and time format. For example, English (United States) returns MM/dd/yyyy hh:mm:ss (04/27/2022 12:34:00).
if...then...else: Conditional operator. Allows for multiple clauses.
in: Takes a column name and a list of values. Checks each column value against the list of values in the formula, and returns true if the column value matches one of the values in the formula.
similarity: Accepts a document text string and a search text string. Returns the relevance score (0-100) of the search string with respect to the document, based on edit distance, number of words in the query, and length of matching words present in the document. An exact match returns 100.