Annotations in Charts

  • 26 November 2020
  • 12 replies
  • 1146 views

Userlevel 3

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
 


12 replies

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

Userlevel 3

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 3

@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 3

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. 

@jamesnestler Do you know if the Looker product team has chart annotation functionality on their roadmap? This is a theoretically helpful hack, but I often want non-technical stakeholders, like our CX team, to annotate why SLA was low or when a bug fix went into production. This is a little overly complicated for quick annotations.

 

We’re currently making the full switch from Mixpanel to Looker and not having this functionality is a BIG thorn in our sides since Mixpanel made annotating charts and graphs really, really easy. 

Userlevel 3

@luciastacey I’m not aware of any specific plans, although I imagine there may be more opportunities for hackery (that’s perhaps more flexible/easy) in coming iterations of visualizations. I think your best bet will be to talk about this in the feedback portal since product actively monitors that forum and considers feedback there as they make feature decisions.

Userlevel 3

I definitely see the utility, it’s something I’d personally like to see too!

Userlevel 1

Hi @jamesnestler,

First of all thanks for the solution, is quite clever :)

However, I am following your steps, but I cannot reach the same output.

I have created same quick calculation (default formatting):

if(row()=1 OR row()=max(offset_list(row(),0,count(${XXX.XXX_month}))),concat("Amount at ", to_string(${XXX.XXX_month}), ": ", to_string(${XXX.XXX_number})),null)

 

 

Then I follow the steps on column chart:

  • Select column 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): here I have the problem. If a turn on value labels both labels from the primary measure and from the calculations appear, so the result is something like that: 

    If I change the color of the label to red or transparent all labels change, the one from the dimensions and from the calculated dimension.

Am I missing something? Could you help me with that?

 

Thanks in advance

Userlevel 3

Nadirfb what you’d want to be doing is this 

 

Userlevel 1

thank you very much @jamesnestler ,

It works, and it will help me a lot

Reply