Annotations in Charts

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
 

1 14 5,254
14 REPLIES 14

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

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.

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

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. 

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

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

Nadirfb
Participant I

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: 
    add28760-5ca7-4781-99b7-9b80015ba776.png
  • 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:
    9d0c177f-9dd2-4ee4-9626-034c9145f582.png
  • 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: 
    cb633c21-43f3-4a55-8eed-0dc191e64976.png

    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

Nadirfb what you’d want to be doing is this 

094f644c-3f40-4334-82dc-56e679e0ba1d.png

Nadirfb
Participant I

thank you very much @jamesnestler ,

It works, and it will help me a lot

Hey @jamesnestler,

To begin with, thanks for a great workaround! 

I’ve just one issue that I can’t really figure out how to get around. In your screenshots it looks as if your annotations are placed neatly next to the value they are describing. I don’t really get that to work, instead the values are placed in a somewhat descending order like this: 

e38b3ce8-9b71-40c0-a9a9-0d365c709c6f.png

Here you can see how they are placed along the axis. 

f55376fa-d22d-484a-8569-c81bb5cf5126.png

Is there any smart way to get the annotations directly next to the values in the graph? I’m using the line chart visualisation above. (I should mention that the Title metric there is created in LookML as a string measure. But I had the same issues when following your example with table calculations.)

Hey @rob_aman, that part will take some finagling. I’ve found that adjusting the y axis min/max for the axis the labels are on usually works. For moving the annotations to the left/right, you might have to do some table calc hackery with row and offset.

Top Labels in this Space
Top Solution Authors