LEAD

Description

LEAD

is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the value of a field in the row at the specified offset after (below) the current row in the group.

The

PARTITION BY

clause determines which fields to use to partition a set of input rows into groups. The

ORDER BY

clause determines how to order the rows in the partition.

Workday separates the input rows into groups according to the partitioning fields, orders the rows according to the ordering fields, and then computes the aggregate expression (lead for this function) in each group.

Syntax

LEAD(input_field, offset, default_value) OVER(
   PARTITION BY
partitioning_field [, partitioning_field]
   ORDER BY
ordering_field [ASC | DESC] [, ordering_field [ASC | DESC]]
   )

Return Value

Returns one value per row of the same type as the input_field.

Input Parameters

input_field: Required. The field on which to perform the aggregate function. You can specify any field type.
offset: Optional. The number of rows after the current row whose value to return. Must be a literal number greater than or equal to zero (0) and less than or equal to 100. If you don't specify the offset, Workday uses the value of 1.
default_value: Optional. The value this function returns when the offset row is outside the currently defined window or when the value in the offset row is NULL. default_value must be the same type as input_field. If you don't specify a default value, Workday uses the value of NULL.
OVER(): Required.
OVER
must be used within a
LEAD
expression.
PARTITION BY partitioning_field: Required. Use the
PARTITION BY
clause to specify 1 or more fields to use to partition a group of input rows. You can specify any field type except Currency. Example: You specify the Month field as the partitioning field, so Workday groups into a single partition all records that have the same value for Month.
ORDER BY ordering_field: Required. Use the
ORDER BY
clause to specify how to order the input rows in the partition using the values in the specified field within each partition. You can specify any field type except Currency.
You can use the
DESC
or
ASC
keywords to sort in descending order (high to low values, NULLs are last) or ascending order (low to high values, NULLs are first) for each ordering field. If you don't specify a sort order for an ordering field, Workday automatically sorts rows in ascending order.

Examples

Example: You have a dataset with these rows and fields.

row_ID	Employee_Name	Eff_Date	Salary
1	Goh	1/1/18	49000
2	Goh	1/1/19	56000
3	Goh	1/1/17	44000
4	Freeman	1/1/18	65000
5	Freeman	1/1/17	57000
6	Freeman	1/1/19	69000
7	Smith	1/1/18	51000
8	Smith	1/1/19	56000
9	Smith	1/1/16	44000

You can order the rows for each employee in descending (DESC
) order by the effective date (Eff_Date
) field, so the most recent salary comes first in each partition.

Use this expression in the Salary_Increase
field to calculate the change in salary between each change in effective date:

[Salary] - (LEAD([Salary], 1, [Salary]) OVER(
     PARTITION BY [Employee_Name] 
     ORDER BY [Eff_Date] DESC)
     )

You get these results:

row_ID	Employee_Name	Eff_Date	Salary	Salary_Increase
2	Goh	1/1/19	56000	7000
1	Goh	1/1/18	49000	5000
3	Goh	1/1/17	44000	0
6	Freeman	1/1/19	69000	4000
4	Freeman	1/1/18	65000	8000
5	Freeman	1/1/17	57000	0
8	Smith	1/1/19	56000	5000
7	Smith	1/1/18	51000	7000
9	Smith	1/1/16	44000	0