Question

Creating Custom Vis via HTML

  • 9 November 2016
  • 4 replies
  • 3191 views

Userlevel 3

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


4 replies

Userlevel 3

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

Userlevel 3

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





Userlevel 2

Here is a 1:1 drop-in replacement for google image charts: https://www.image-charts.com/

Free version available (with watermark), but paid version is reasonably priced.


Quickchart looks to be a free and open source option, but the URL scheme is different so a little more lift to transition to: https://github.com/typpo/quickchart

Userlevel 3

Scott, you’re a real one! Thanks for the resources!

Reply