Calculate Week Over Week Numbers

Posted on
October 19, 2018

Once you calculate your weekly numbers, calculating week over week numbers become 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 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.
Posted on
October 19, 2018
in
Tips & Tricks
category

Ready to Speak Sigma?