Variable Reference Lines for Charts

[LookerExperts]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

Author: Mark Stephens

With Looker it is easy to add a straight horizontal reference line to a visualization. But what if you had variable monthly or quarterly targets, and you needed to show the varying targets? You can create a line whose height varies in different months or quarters. You can do this in LookML by creating a measure that hard-codes the correct values to show in that variable reference line.

Examples
 

Since we use the `sql` parameter, the code to create the variable reference lines may differ by the database dialect your project is using.

The code to do this, written for Redshift dialect:

measure: target_test {
   type: number
    sql:
     (max 
      (case when to_char(${TABLE}.date, 'YYYY-MM') in ('2015-01','2015-02', '2015-03') then 12000
      when to_char(${TABLE}.date, 'YYYY-MM') in ('2015-04','2015-05', '2015-06') then 12500
      when to_char(${TABLE}.date, 'YYYY-MM') in ('2015-07','2015-08', '2015-09') then 13000
      when to_char(${TABLE}.date, '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)) ;;
}