Salesforce Docs Portal

Window Functions for Formulas

Use window functions to perform calculations across rows. For each row of input data, perform a calculation on a selection—or window—of related rows. Unlike aggregation functions that group rows, window functions return a result for each row. For example, you can calculate the changes to the opportunity amount throughout the stages of each opportunity to see if you can hit your sales target.

When entering a formula, use the API name of the field in the expression, not the label. Surround the parameters with parentheses.

You can use the following window functions in a formula expression.

String Function Description Syntax
collect_list Returns a array type containing duplicates.

collect_list(field)

field is the field for which you want all the values for.

Example: Return all the values from the Owner ID column.

Owner Id
--------
A
B
C

array_join(collect_list(OwnerId), ',') -> A,B,C
collect_set Returns an array type without duplicates.

collect_set(field)

field is the field for which you want remove duplicate values from.

Example: Return only the unique values from the Owner ID column.

Owner Id
--------
A
B
C

array_join(collect_set(OwnerId), ',') -> A,B,C
current Returns the value from the current record in the partition.

current(field)

field is the field that you want to get the current value from.

Example: Get the value of the Amount field for the current row.

current(Amount)
dense_rank Returns the rank of each record in the partition based on order. Ranks are consecutive—they don’t repeat when the values match. For example, if the first three rows have the same value, then the first four ranks are 1, 1, 1, and 2.

dense_rank()

Example: Calculate the dense rank for rows within each partition.

dense_rank()
first_value Returns the value from the first record in the partition.

first_value(field)

field is the field that you want to get the first value from.

Example: Get the original quote value for the Quote field.

first_value(Quote)
lag Returns the value from the previous record in the partition.

lag(field)

field is the field for which you want to obtain the previous value.

Example: Calculate the percentage change in profit between the current and previous records.

(((current(Profit)-lag(Profit)))/current(Profit))*100
last_value Returns the value from the last record in the partition.

last_value(field)

field is the field that you want to get the last value from.

Example: Calculate the difference between the original quote and final quote.

first_value(Quote)-last_value(Quote)
lead Returns the value from the next record in the partition.

lead(field)

field is the field for which you want to obtain the next value.

Example: Get the next value for the Stage field.

lead(Stage)
rank Returns the rank of each record in the partition based on order. Repeats the rank when the values are the same, and skips as many on the next non-match. For example, if the first three rows have the same value, then the first four ranks are 1, 1, 1, and 4.

rank()

Example: Rank the records in each partition.

rank()
row_number Returns the row number in the partition. Increments by 1 for every row in the partition. For example, if the first three rows have the same value, then the first four ranks are 1, 2, 3, and 4.

row_number()

Example: Assign a row number to each record in each partition.

row_number()
sum Returns the cumulative sum from groups of values.

sum()

Example: Track the cumulative number of monthly units shipped tied to a specific sales agreement.

sum()