Support received a question on how to calculate percent change in sales week over week. As this comes up fairly often, I wanted to share how you can do this!
Here we have a table of weekly sales data.
The formula in the +/- Last Week column is:
last_week_date = DATEADD($Date, weeks=-1)
last_week_sales = Daily_Sales.lookupOne(Date=last_week_date).Sales
($Sales - last_week_sales)/last_week_sales if last_week_sales else 0
Let’s break this down line by line.
DATEADD($Date, weeks=-1): We use DATEADD() to find the date that is one week prior to this record’s date. Looking at row 1, one week prior to 05/10/2023 would be 05/03/2023. We assign this date to the variable last_week_date.
Daily_Sales.lookupOne(Date=last_week_date).Sales: We use a lookupOne formula to find the record in the Daily Sales table where the value in the Date column matches the value found for last_week_date. We then use dot notation to pull the Sales value for that record. For the record with date 05/03/2023, the sales would be 1200. We assign this value to the variable last_week_sales
($Sales - last_week_sales)/last_week_sales: Finally, we do the actual calculation to compare sales.
if last_week_sales else 0 gets rid of the error for the oldest record where no prior week exists.
This formula returns the % as a decimal so be sure to change the column type to Numeric and select %
