Year over year (YoY) SQL View example

It is often useful to compare months in varying years, and here is an example of how you would achieve that using a SQL view.

If you are unfamiliar with SQL views, then we'd recommend reading through these docs first.

The SQL view

Here is a Postgres SQL snippet that can be used to render YoY comparisons. You will need to modify the data set and field IDs so that they match your data set and field IDs.

SELECT 
  -- Build the month grouping with month number prefix for accurate ordering
  to_char(vizzly_data_set_nyino0.vizzly_field_1ko27d, 'MM Month') as "fie_1|group&name=Month",


  -- Build the year grouping
  extract(year from vizzly_data_set_nyino0.vizzly_field_1ko27d) as "fie_2|group&name=Year",


  -- Build the metric you wish to show. Here it is a simple count
  count(vizzly_data_set_nyino0.vizzly_field_1ko27d) as "fie_3|metric&name=Employee count"


-- load results from the data set where you have the fields you wish to use
from vizzly_data_set_nyino0


-- Group and order by month & year
GROUP BY 1, 2
ORDER BY 1, 2

Making it your own?

Change vizzly_data_set_nyino0 to the ID of your Vizzly data set.
Change vizzly_field_1ko27d to the ID of your date field.
Change the metric to something you want to measure YOY.
Change the public names by altering the name= property in the as string.

On the dashboard

Once set up correctly, you will be able to select your SQL view from the "data sets" dropdown on the view editor.

If your SQL view is not appearing on the dashboard, it is likely there is a syntax error with your SQL.

SQL Views Time zones