Annotations in Charts

  • 26 November 2020
  • 6 replies
  • 272 views

Userlevel 2
Badge

Caveat: This has only been tested for Line and Bar charts. Not all chart types allow the vis config customizations that this approach relies on. Additionally, this is not an “official” workflow, it’s more of a hack that could potentially break at any point in the future. That said, I’m documenting it here because I think it’s cool & some folks might find it helpful :)

 

Problem:
You’d like to be able to add notes to a visualization, perhaps to call out a certain row of data, or add short comments to a visualization at specific points. 

 

Solution:

Starting with a table like so:

image.png

  1. Create a table calc that outputs the annotation text at the desired row(s), and null for all other rows

If, for example, we wanted to show an annotation of "Orders at 2019-01: 941" at the first row, and "Orders at 2019-12: 1002" at the last row, we could use this to evaluate true for only those 2 rows:

row()=1 OR row()=max(offset_list(row(),0,count(${sc_orders.output_dim})))

 

And this to display the desired text:

concat("Orders at ", to_string(${created_date}), ":", to_string(${orders.count}))

Put it all together and we have:

if(row()=1 OR row()=max(offset_list(row(),0,count(${sc_orders.output_dim}))),concat("Orders at ", to_string(${sc_orders.output_dim}), ": ", to_string(${sc_orders.output_measure})),null)

 

  1.    Now some Vis hackery. Do the following:
  • Select line chart for vis type
  • In Plot settings, turn off plot null values
  • Select bar chart for vis type
  • In Series > Customizations, make the primary measure a line and change the color of the table calc to white
  • In Y menu settings, drag the table calc from 1 into the right Y axis, select the second y axis and turn off show axis name and show axis values
  • In Values menu, turn on value labels. If Value Labels for other fields are not desired, in value colors input "transparent, red" (can be any color)
  • Voila!

A few examples of what this can end up looking like:

image.png

image.png
 


6 replies

The images you uploaded are broken for me. Are you able to re-upload them? @jamesnestler 

Userlevel 2
Badge

Hey @AK0001 , they might take some time to load. The community site seems to be a bit slow in loading assets sometimes.

The images you uploaded are broken for me. Are you able to re-upload them? @jamesnestler 

They will not load for me either.

Userlevel 2
Badge

@collinprather @AK0001 

Example table: https://ibb.co/1rHsrxV

Example output:

@jamesnestler 

Thanks so much for the response!

 

So, one of the challenges I ran into was wanting to add an annotation that was a hard-coded string. Ie, something like “New Product Launch”. When I hard-coded this in the table calculation, however, looker then recognizes it as a dimension, not measure, so I’m unable to carry-out the viz hackery in step 2.

 

Is there any further hackery that can workaround that issue?

Userlevel 2
Badge

Yeah - you’d have to “measurize” the table calc. Basically just reference a measure anywhere in that table calc’s definition (the measure can be hidden, arbitrary, etc it doesn’t matter). A really easy pattern I usually use is just pull in a simple count measure, hide it, then do something like:

(${count}*0)+ the rest of your table calc definition. 


Since this is a string you’re trying to measurize, you’ll have to do something a bit more hacky using substring and concat like:

substring(concat((${count}*0),”Your String”),2,length(“Your String”))

There’s probably a syntax error or two in there ^ but hopefully it gives you an idea. 

Reply