Variable Reference Lines for Charts


With Looker it is easy to add a reference line to a visualization.

For example suppose you had a order target of 12500 units. We can add a simple reference line using visualization options:

Which will give us a nice reference line against our monthly data.

But what if you had variable monthly or quarterly targets?

You can accomplish this by creating a Looker measure that hard codes the values.

The code to do this, written for Redshift dialect:

  - measure: monthly_target
    type: number
    sql: |
      (max(case when to_char(${TABLE}.created_at, 'YYYY-MM') in ('2015-01','2015-02', '2015-03') then 12000 
                when to_char(${TABLE}.created_at, 'YYYY-MM') in ('2015-04','2015-05', '2015-06') then 12500
                when to_char(${TABLE}.created_at, 'YYYY-MM') in ('2015-07','2015-08', '2015-09') then 13000
                when to_char(${TABLE}.created_at, 'YYYY-MM') in ('2015-10','2015-11', '2015-12') then 13500
                else null end))

The same code written for MySQL dialect:

  - measure: monthly_target
    type: number
    sql: |
      (max(case when date_format(${TABLE}.created_at, '%Y-%m') in ('2015-01','2015-02', '2015-03') then 12000 
                when date_format(${TABLE}.created_at, '%Y-%m') in ('2015-04','2015-05', '2015-06') then 12500
                when date_format(${TABLE}.created_at, '%Y-%m') in ('2015-07','2015-08', '2015-09') then 13000
                when date_format(${TABLE}.created_at, '%Y-%m') in ('2015-10','2015-11', '2015-12') then 13500
                else null end))

Options for setting target or comparison values in tables and graphs
(Bridge ) #2

Is there a more simplistic way to do this for monthly targets?

I’m thinking it may make more sense to just create an entire “goals” table in Redshift vs. a measure for every month & metric.


(Justin Michaels) #3

Thanks, Mark. Very helpful way to get more out of y-axis reference lines. Now the big question for me is when will Looker allow for easy annotation of x-axis data. For example, It would be helpful to be able to add labels or at least lines on dates where important things happened (a product change, a launch date, etc). Please pass this along to the team as a feature request :slightly_smiling:




Yes if you had the ability to create a physical table you can join that into an Explore and reference the measure in the Look. You can also create a derived table within Looker and manually feed in the numbers.This is a simple method for when creating a physical table is not an option and you only have a few data points.




Great feedback will pass along to the product team.


(Bridge ) #6

@markrstephen Additionally, one thing that has surprised me that Looker doesn’t support is multiple horizontal reference lines.

For example, if I am showing data pivoted on A & B, only the first column (A) can be automatically given an “average” reference line.

1 Like



Thanks for the feedback will pass this along.


(Dave Masom) #8

I’d like to +1 Mark’s request for the ability to annotate the x-axis and Bridge’s request to have reference lines for multiple series.

My particular use case for the x-axis request - we have a chart showing a cumulative total of enrollments we received in the past week, and how long it took us to reach out to them. I’d like to be able to put a line at the 24 hour mark (as that is our target for when we would like to have contacted everyone by)


(Nolan) #9

+1 for reference lines on a series other than the first. This seems like an unnecessary limitation where trend lines can apply to any series, but reference lines cannot.

from docs:

Reference lines are applied to the first series in your query results (the leftmost column in the result set), even if that series is hidden from the visualization. You can reorder your columns to change the series the reference line is applied to.