Snippet - Month Versus Month in Prior Year
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)
Please sign in to leave a comment.
Comments
0 comments