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)
