YTD Sum Vs Prior YTD Sum

[intapiuser]

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