Row functions - Lightdash

Row functions let you reference values from other rows without writing raw SQL window functions. They use the ${table.column} syntax to reference fields in your results table.

Row functions use your query’s current sort order to determine row positions. Changing the sort order of your results will change the output of these functions.

Row functions operate across your entire results table, with no equivalent to SQL’s PARTITION BY. If a dimension in your rows splits the data into categories (for example a region or product column), offset, index, offset_list, lookup, and row don’t reset at each category — an offset(${orders.total_revenue}, -1) can return a value from a different category’s row. If you need an offset or lag that resets per group, either:

Use the Formula table calculation’s window functions (for example LAG), which accept an inline PARTITION BY clause, or
Pivot the dimension into columns and use pivot functions like pivot_offset, which partition by row automatically.

row

Returns the 1-based row number of the current row.

row()

Parameters: None Example Add a row number column to your results:

row()

Compiled SQL

ROW_NUMBER() OVER (ORDER BY ...)

The ORDER BY clause uses the sort order configured in your query.

offset

Returns the value of a column from a row at a relative offset from the current row.

offset(column, rowOffset)

Parameter	Type	Description
`column`	column reference	The column to get the value from
`rowOffset`	integer	Number of rows to offset. Negative = previous rows, positive = next rows, 0 = current row

offset counts rows across your whole results table, in sort order — it doesn’t restart at each value of a dimension. This makes it safe when your results have one row per step (for example a single time dimension), but unreliable once another dimension adds more than one row per step: with three categories alongside a weekly time dimension, offset(x, -52) moves back about 17 weeks, not 52, because it also steps across the other categories’ rows. To offset within a group, use a formula table calculation with PARTITION BY, which restarts LAG/LEAD for each value of a dimension. For a prior-year or prior-period comparison on a chart with extra dimensions, use period comparison, which compares each group against its own prior period. Example Get the previous row’s revenue to calculate period-over-period changes:

${orders.total_revenue} - offset(${orders.total_revenue}, -1)

Compiled SQL

For negative offsets (previous rows):

LAG(${orders.total_revenue}, 1) OVER (ORDER BY ...)

For positive offsets (next rows):

LEAD(${orders.total_revenue}, 1) OVER (ORDER BY ...)

For an offset of 0, the column value is returned directly with no window function. None of these include a PARTITION BY, so the offset always spans the whole results table.

index

Returns the value of an expression from an absolute row position (1-based).

index(expression, rowIndex)

Parameter	Type	Description
`expression`	column reference or SQL expression	The expression to evaluate
`rowIndex`	integer (≥ 1)	The 1-based row position to get the value from

Example Compare every row’s revenue against the first row’s revenue:

${orders.total_revenue} / index(${orders.total_revenue}, 1)

Compiled SQL

NTH_VALUE(${orders.total_revenue}, 1) OVER (
  ORDER BY ...
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

lookup

Finds a value in one column and returns the corresponding value from another column.

lookup(value, lookupColumn, resultColumn)

Parameter	Type	Description
`value`	any	The value to search for
`lookupColumn`	column reference	The column to search in
`resultColumn`	column reference	The column to return the value from

If multiple rows match, the largest matching value from resultColumn is returned. Example Look up the revenue for a specific status:

lookup('completed', ${orders.status}, ${orders.total_revenue})

Compiled SQL

MAX(
  CASE WHEN ${orders.status} = 'completed'
    THEN ${orders.total_revenue}
    ELSE NULL
  END
) OVER ()

offset_list

Returns an array of values from consecutive rows starting at a relative offset.

offset_list(column, rowOffset, numValues)

Parameter	Type	Description
`column`	column reference	The column to get values from
`rowOffset`	integer	Starting row offset. Negative = previous rows, positive = next rows, 0 = current row
`numValues`	integer	Number of consecutive values to include

Example Get the current and two previous revenue values (a 3-period window):

offset_list(${orders.total_revenue}, -2, 3)

Compiled SQL

ARRAY[
  LAG(${orders.total_revenue}, 2) OVER (ORDER BY ...),
  LAG(${orders.total_revenue}, 1) OVER (ORDER BY ...),
  ${orders.total_revenue}
]

list

Constructs an array from multiple values. Unlike the other row functions, list does not use any windowing.

list(value1, value2, ..., valueN)

Parameter	Type	Description
`value1...valueN`	any	Values or expressions to combine into an array

Example Create an array of specific column values:

list(${orders.total_revenue}, ${orders.total_cost}, ${orders.total_profit})

Compiled SQL

ARRAY[
  ${orders.total_revenue},
  ${orders.total_cost},
  ${orders.total_profit}
]

​row

​offset

​index

​lookup

​offset_list

​list

row

offset

index

lookup

offset_list

list