Calculate Week Over Week Numbers

Posted on
May 17, 2019

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
May 17, 2019
in
Tips & Tricks
category

See Cloud-Native Analytics in Action