Lag Lead function

LAG

It returns the value of the column in the row that is offset rows after the current row within the result set. If the offset goes beyond the end of the result set, the default value is returned.

Syntax – LAG(aggregate function with column name), preceding integer, forward integer) OVER ([PARTITION BY attribute_name] ORDER BY attribute_name)

Example: Here we are returning the value of sales in the offset row after the current row concerning the region.

Example Queries:

• LAG(SUM([Retail].[Sales]))
  OVER (PARTITION BY [Retail].[Country] ORDER BY [Order Date year])
  (note: if data has null values use ZEROIFNULL)
• LAG(MAX([Retail].[Quantity]))
  OVER (PARTITION BY [Retail].[Region] ORDER BY [Order Date year])
• LAG(MAX([Retail].[Order Discount]))
  OVER (PARTITION BY [Retail].[Category] ORDER BY [Order Date year])
  (note: if data has null values use ZEROIFNULL)
• LAG(SUM([Retail].[Sales]))
  OVER (PARTITION BY [Retail].[State] ORDER BY [Retail].[Order Date])
  (note: if data has null values use ZEROIFNULL)
• ROUND(((SUM([Retail].[Sales]) – LAG(SUM([Retail].[Sales])) over (order by [Order Date year] ASC))/LAG(SUM([Retail].[Sales])) over (order by [Order Date year] ASC))*100, 2)
  (note: if data has null values use ZEROIFNULL)

LEAD

It returns the value of the column in the row that is offset rows before the current row within the result set. If the offset goes beyond the end of the result set, the default value is returned.

Syntax – LEAD(aggregate function with column name), preceding integer, forward integer) OVER ([PARTITION BY attribute_name] ORDER BY attribute_name)

Example: Here we are returning the value of sales in the offset row before the current row concerning the region.

Example Queries:

• LEAD(SUM([Retail].[Sales]))
  OVER (PARTITION BY [Retail].[Country] ORDER BY [Retail].[Order Date Year])
  (note: if data has null values use ZEROIFNULL or NULLIFZERO)
• LEAD(MAX([Retail].[Quantity]))
  OVER (PARTITION BY [Retail].[Region] ORDER BY [Order Date year])
  (note: if data has null values use ZEROIFNULL or NULLIFZERO)
• LEAD(MAX([Retail].[Order Discount]))
  OVER (PARTITION BY [Retail].[Category] ORDER BY [Order Date year])
  (note: if data has null values use ZEROIFNULL or NULLIFZERO)
• LEAD(SUM([Retail].[Sales]))
  OVER (PARTITION BY [Retail].[State] ORDER BY [Retail].[Order Date])
  (note: if data has null values use ZEROIFNULL or NULLIFZERO)
• ROUND(((SUM([Retail].[Sales]) – LEAD(SUM([Retail].[Sales])) over (order by [Order Date year] ASC))/LEAD(SUM([Retail].[Sales])) over (order by [Order Date year] ASC))*100, 2)
  (note: if data has null values use ZEROIFNULL or NULLIFZERO)

Note: If you apply the PARTITION BY clause in the LAG and LEAD functions, and the partitioned column is not used in the “Ask Me” or “Self-Service” attribute section, the query may encounter errors or unexpected behaviour.