Window Functions for Formulas

In CRM Analytics, use window functions in a Data Prep recipe to perform calculations across rows. For each row of input data, perform a calculation on a selection—or window—of related rows. Unlike aggregation that groups 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.

Window Functions for Formulas

When entering a formula, use the API name of the column 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
`current`	Returns the value from the current record in the partition.	`current(field)` `field` is the field 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 you want to get the first value from. Example: Get the original quote value for the Quote column. `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 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 column. `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 of each record in a partition based on order. When rows have the same values for the order-by column, the cumulative sum is calculated for all the rows with the same column value.	`sum()` Example: Track the cumulative number of monthly units shipped tied to a specific sales agreement. Example: Add OrderBy grouping to Activity Date. Then, 2 rows with the same Activity Date and values of 2000 and 11000 both show the cumulative sum of 13000. `sum()`
`collect_list`	Returns a multivalue 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. `collect_list(OwnerId)`
`collect_set`	Returns a multivalue without duplicates.	`collect_set(field)` `field` is the field for which you want to remove duplicate values from. Example: Return only the unique values from the Owner ID column. `collect_set(OwnerId)`

Did this article solve your issue?

Let us know so we can improve!

Window Functions for Formulas