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)) ;;
}

Articles in this section

Variable Reference Lines for Charts

Examples