Calculate Week Over Week Numbers

Once you calculate your weekly numbers, calculating week over week numbers becomes very simple when using the Lag or Lead functions. Lag and Lead are Window Functions that shifts all of the rows in a column. The key to using Lag and Lead properly is to ensure that your data is sorted in a unique order, with no “ties” in the sort order. Using Multi-Column Sort Criteria is the best way to ensure that.

 

Calculate Week over Week numbers using Lag:

  1. Sort weekly sales data by date in Ascending order, ensuring a unique sort order.
  2. Once you have your data sorted, create a new column and enter in [WEEKLY SALES]/ Lag ([WEEKLY SALES])
  3. Change the column format to percent. 

 

Calculate Week over Week numbers using Lead:

  1. Sort weekly sales data by date in Descending order, ensuring a unique sort order.
  2. Once you have your data sorted, create a new column and enter in [WEEKLY SALES]/ Lead ([WEEKLY SALES])
  3. Change the column format to percent.