Amazon QuickSight – Difference

The Amazon QuickSight difference() function is used to calculate the difference between the current value and a previous value for a given measure, based on the sort order and partitioning specified. This function can be used to calculate changes in data over time, among other applications.

Syntax

#Start#
difference(
    measure, 
    [ sortorder_field ASC_or_DESC, ... ],
    lookup_index,
    [ partition field, ... ]
)
#End#

This function takes the following arguments:

• measure: This is the name of the measure that you want to calculate the difference for.

• sortorder_field ASC_or_DESC: This parameter is optional, and is used to specify the fields by which you want to sort the data. You can specify multiple fields, each followed by either “ASC” (ascending) or “DESC” (descending). This determines the order in which the data is sorted before the difference is calculated.

• lookup_index: This parameter is optional, and is used to specify the number of rows to look back when calculating the difference. The default value is 1, which means that the difference is calculated between the current row and the previous row.

• partition field: This parameter is optional, and is used to specify the fields by which you want to partition the data. This means that the difference is calculated separately for each unique combination of values in these fields.

Example

#Start#
difference(SUM(Sales), [OrderDate ASC], 1, [Product])
#End#

This calculates the difference between the current row and the previous row for the “Sales” measure, sorted by “OrderDate” in ascending order, looking back 1 row, and partitioning by “Product”. This means that the difference is calculated separately for each unique combination of products, and is based on the difference in sales between the current row and the previous row.