Amazon QuickSight – Lag

The Amazon QuickSight “lag” function is a table calculation function that allows you to compute the difference between a measure or dimension value in the current row and a value in a previous row.

Syntax

#Start#
lag(
     measure or dimension field 
     ,[ sortorder_field ASC_or_DESC, ... ]  
     ,lookup_index,
     ,[ partition_field, ... ] 
)
#End#

This function takes the following arguments:

measure or dimension field: the field that you want to calculate the difference for. It can be a measure or a dimension field.

[sortorder_field ASC_or_DESC, …]: optional parameters that allow you to specify the sorting order of the data within a partition. You can specify multiple sort order fields, and each can be sorted in either ascending (ASC) or descending (DESC) order.

lookup_index: an integer value that specifies the number of rows back from the current row that you want to look up the value for. For example, if lookup_index is set to 1, the function will look up the value of the field in the previous row.

[partition_field, …]: optional parameters that allow you to specify the partition fields. The function will calculate the lag value for each distinct value of the partition fields.

Here’s an example of the “lag” function in action:

Suppose you have a dataset that contains sales data for a store, and you want to calculate the difference between the current day’s sales and the previous day’s sales. You can use the “lag” function to achieve this.

Assume that the dataset has the following fields: “Date” (dimension), “Sales” (measure).

The following formula will calculate the difference between the current day’s sales and the previous day’s sales:

Example

#Start#
lag(Sales, 1, [Date])
#End#

This formula looks up the sales value for the previous date (using lookup_index set to 1) and partitions the data by the “Date” field.