Amazon QuickSight – Lead

The Amazon QuickSight lead function is used to return the value of a specified measure or dimension from a specified row after a given offset number of rows. It can be used to analyze trends in data by looking at changes in the values of a specific field over time or over other dimensions.

Syntax

#Start#
lead
(
     measure
     ,[ sortorder_field ASC_or_DESC, ... ]  
     ,lookup_index,
     ,[ partition_field, ... ]
)
#End#

This function takes the following arguments:

• measure: The measure or dimension field for which to retrieve the lead value.

• sortorder_field: Optional. A field to use as the sort order when determining the row to retrieve the lead value from. If not specified, the default sort order is ascending.

• lookup_index: Optional. The number of rows after the current row to retrieve the lead value from. The default is 1, which returns the value of the next row.

• partition_field: Optional. One or more fields to use for partitioning the data before computing the lead value.

Suppose you have a dataset that contains monthly sales figures for a company, and you want to calculate the month-to-month percentage change in sales. You can use the lead function to retrieve the sales figure for the next month, and then divide it by the current month’s sales figure to get the percentage change.

Here’s an example of the lead function in action:

Example

#Start#
lead(sales, order by month asc, 1) / sales - 1
#End#

This calculates the percentage change in sales from one month to the next, by dividing the sales figure for the next month by the current month’s sales figure and subtracting 1. The order by clause specifies that the rows should be sorted by month in ascending order, and the 1 parameter in the lead function specifies that the lead value should be retrieved from the next row.