Creating Custom Vis via HTML

(jake) #1

Create guage charts or sparklines in line with your data set!

You can use Google’s Charts to pass in data via a URL and get a chart back! This allows you to produce a graph in your data table, or easily support non-native chart types, (like gauges). IMPORTANT: The Google documentation says that the URL version of the API is deprecated. The warning says that they do not have plans to remove it but it may go away. Use at your own risk.

The general pattern is to create a field that formats the desired data in a way such that you can pass it in to Google via the URL to get a chart. Then, use the Looker html parameter and the img tag to choose the appropriate chart settings. E.g.:

  measure: sold_percent_gauge {
    type: number
    sql: 100.0*${count_sold}/nullif(${count},0) ;;
    value_format: "#.0\%"
    html:   <img src="https://chart.googleapis.com/chart?chs=400x250&cht=gom&chma=10,0,0,0&chxt=y&chco=635189,B1A8C4,1EA8DF,8ED3EF&chf=bg,s,FFFFFF00&chl={{ rendered_value }}&chd=t:{{ value }}">;;
  }

Let’s break the paramters of the google url down:

chs: chart size (WxH)
cht: chart type (in this case gom = google-o-meter = gauge)
chxt: which labels to add
chco: chart colors - can put in hex
chf: chart fill (bg= background, s = solid, FFFFFF00 - transparent - this will match the background)
chl: chart label (rendered_value gives formatting)
chd: chart data (t means text format)

Now that I have my measure defined, I can select this measure and select the single value viz type from Looker and put it on a dashboard as a gauge:

It can also be dynamically grouped.
(Although you may want to make a smaller chart size for this use case).

The example above is simple because it graphs a single value. But we can also pass a list of data into the url to graph a series of data. For redshift, you can use the LISTAGG window function.

Again, the pattern is to use SQL to get data into the appropriate format for the chart type.

In this case, we will use three derived tables to get the data we want, cross the dates and brands to zero fill, and finally apply LISTAGG to calculate, as a dimension, for each brand, the last 30 days of sales data:

view: product_query {
  # Let Looker write this query for BRAND, DATE, measureing SALES and ORDER for 30 days
  derived_table: {
    sql: SELECT
        DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', order_items.created_at)) AS created_date,
        products.brand AS brand,
        COUNT(DISTINCT order_items.id) AS orders_count,
        SUM(order_items.sale_price) AS sales
      FROM public.order_items AS order_items

      LEFT JOIN public.inventory_items AS inventory_items ON order_items.inventory_item_id = inventory_items.id
      LEFT JOIN public.products AS products ON inventory_items.product_id = products.id
      WHERE
        (((order_items.created_at) >= ((CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', DATEADD(day,-29, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', GETDATE())) )))) AND (order_items.created_at) < ((CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', DATEADD(day,30, DATEADD(day,-29, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', GETDATE())) ) ))))))
      GROUP BY 1,2 ;;
  }
}

view: product_possible_values {
  # Get all the possible values for dates and brands combinations so we can zero fill.
  derived_table: {
    sql: SELECT date, brand FROM
        (SELECT DISTINCT created_date as date FROM ${product_query.SQL_TABLE_NAME}) as dates
      CROSS JOIN (SELECT DISTINCT brand FROM ${product_query.SQL_TABLE_NAME}) brands ;;
  }
}

view: product_sparklines {
  derived_table: {
    sql: SELECT
        pv.brand
        , LISTAGG(COALESCE(pq.sales,0.0),',') WITHIN GROUP (ORDER BY pv.date) as sales
        , LISTAGG(COALESCE(pq.orders_count,0),',') WITHIN GROUP (ORDER BY pv.date) as orders
      FROM  ${product_query.SQL_TABLE_NAME} as pq
      RIGHT JOIN  ${product_possible_values.SQL_TABLE_NAME} as pv
        ON pv.date = pq.created_date
         AND pv.brand = pq.brand
      GROUP BY 1 ;;
  }
  dimension: brand {hidden: yes}
  dimension: sales{hidden: yes}
  dimension: orders{hidden: yes}

  dimension: brand_sales_30_days{
  sql: '1';;
  html:
  <img src="https://chart.googleapis.com/chart?chs=200x50&cht=ls&chco=0077CC&chf=bg,s,FFFFFF00&chds=a&chxt=x,y&chd=t:{{sales._value}}&chxr=0,-30,0,4">
;;
 }
  dimension: brand_orders_30_days{
  sql: '1';;
  html: |
  <img src="https://chart.googleapis.com/chart?chs=200x50&cht=ls&chco=0077CC&chf=bg,s,FFFFFF00&chds=a&chxt=x,y&chd=t:{{orders._value}}&chxr=0,-30,0,4">
  ;;
  }
}

The url parameters for this:
cht=ls: line series
chds: chart data scale - setting to a makes it automatic
chxr: Sets the x-axis range and increment

Check it out as an explore or on a dashboard

Google Chart Resources
Live Chart Playground lets you play with different chart parameters
Getting Started with google charts

22 Likes

(Brayden) #2

Wow, just saw this and thought this epic hack deserved a bump. Nice one!

1 Like

(jeffrey.martinez) #3

Sad day on Monday! Google deprecated this API on March 18th, 2019


0 Likes