YTD Sum Vs Prior YTD Sum
This parameterized snippet will calculate the sum of a metric for the current YTD vs prior years to current date (day of year).
Name Ytd_sum(Table,Date_field,Value_field)
select
year
, [value_field]
, ([value_field] / nullif(lag([value_field])
over(order by year), 0)) - 1 as percent_change
from
(
select
extract(year from [date_field]) as year
, sum([value_field]) as [value_field]
from
[table]
Where extract(doy FROM ([date_field]::timestamp)) <= [now():day_of_year]
group by
1
) annual
0 comments
