Relative sales variance including DATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

Google Cloud Data Analytics
,
1

Hi all,

Looking to create a metric, LY_sales_variance, for yesterday (relative)

So for example:

Sales today (06/12/2023): 100

Sales today - 364 days (07/12/2022): 80

LY_sales_variance would show 0.2

I need this to roll with yesterday relative which is where I am running into issues.

I found this query which comes close, but is based on years, not sure how to alter to a date specific and incorporate the DATE_SUB:

Any help on this greatly appreciated!

2

Here’s how you can create a metric for yesterday’s LY_sales_variance in BigQuery, rolling with yesterday’s date:

-- **Here's how you can create a metric for yesterday's LY_sales_variance in BigQuery, rolling with yesterday's date:**

WITH yesterday_sales AS (
  SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS date,
         SUM(sales) AS sales
  FROM your_table
  WHERE DATE(date) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  GROUP BY date
), last_year_sales AS (
  SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY) AS date,
         SUM(sales) AS sales
  FROM your_table
  WHERE DATE(date) = DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
  GROUP BY date
)
SELECT y.date AS yesterday,
       y.sales AS sales_yesterday,
       ly.sales AS sales_last_year,
       (y.sales - ly.sales) / ly.sales AS LY_sales_variance
FROM yesterday_sales y
CROSS JOIN last_year_sales ly;

This query does the following:

  1. yesterday_sales CTE: This creates a temporary table containing the aggregated sales for yesterday.
  2. last_year_sales CTE: This creates another temporary table that aggregates the sales for the same date last year.
  3. Main Query: This joins the two CTEs and calculates the LY_sales_variance. This is done by subtracting the sales of the same day last year from yesterday’s sales and then dividing by the sales of the same day last year.
  4. Date Calculation: The query uses DATE_SUB to calculate both yesterday’s date and the date one year ago, ensuring the comparison is always relative to the current date.
3

Really helpful thank you so much!

C