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.



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)