Snippet - Month Versus Month In Prior Year

[intapiuser]

Here's a Parameterized Snippet to calculate the metrics versus the same month in the prior year. Ideally the metrics have already been calculated in a CTE or View and can easily be plugged into the template.

Name: month_versus_prior_year(table,field,date)

select
  current_year.date
  , current_year.[field] as current_year_metric
  , prior_year.[field] as prior_year_metric
  , case
    when prior_year.[field] is null
      then null
    else 1.0 * current_year.[field] / prior_year.[field] - 1
  end as period_over_period_change_perc
  , case
    when prior_year.[field] is null
      then null
    else current_year.[field] - prior_year.[field]
  end as period_over_period_change_value
  , case
    when prior_year.[field] is null
      then null
    else (case when prior_year.[field] > current_year.[field] then 'negative' else 'positive' end)
  end as period_over_period_change_direction
from
  (
    select
      [field]
      , [[date]:month] as date
    from
      [table]
  ) current_year join (
    select
      [field]
      , [[date]:month] as date
    from
      [table]
  )
  prior_year on current_year.date = DATE_ADD(prior_year.date, INTERVAL 1 year)

The above is for Postgres. In Redshift, the last line has to be:

prior_year on current_year.date = DATEADD(year, 1, prior_year.date)