How to Mark Key Dates: Vertical / X-Axis Highlights

Looking to call out critical dates or date ranges? Do you want to do this on pivoted data?

Some example use cases for this include: highlighting promotion or event dates, drawing attention to specific trends / insights, or marking time intervals (e.g. highlighting the start of each week).

We can do this using table calculations:

  • First, create a table calculation to obtain the height of the vertical bar you’d like to plot. In most cases this will be the maximum y-value (such that the highlighted range extends to the top of the chart).
    c19095ec3b4ba551f6c8fc75af52aab75f94abbe.png
  • Second, create a table calculation that specifies which dates we’d like to highlight. If we have pivoted columns, we can use the pivot_index function to collapse them into a single column. For example, here is the table calculation we’d use to highlight the date range 6/7/18 to 6/11/18:
    34c9c2602ff322a718ec3ff700029bce6b35f70a.png

Now we have all the components to create our visualization:

  • Hide the intermediate table calculation from the visualization
  • Select a mixed chart type: line for data you’d like to plot, column for the date marker
7 3 6,266
3 REPLIES 3

Katrien
Participant I

This is more or less what I was looking for. Some potential refinements I have in mind but do not know how to start on them :

  • Can we show Value Labels only for the lines and not the bars representing the reference dates?

  • Would there be a way to have a different resolution on the X axis as compared to your key dates, e.g. having weeks on the X-axis and being able to mark any day of that week as the key date (now it has to be exactly the first day of that week)?

Would it be ever become possible to just draw a simple vertical reference line (like we can for horizontal lines)?

Thanks a lot,
Katrien

This is something that we’ve been thinking about for a long time— You can add your voice and context to the discussion here: https://discourse.looker.com/t/vertical-reference-lines/873, and also check out some of the workarounds, one might work for you (though they’re similar to what’s described here)

This isn’t natively included, but check out @Morgan1’s sweet method here:

The “transparent” color in Looker can be used to hide many different elements of a visualization. One useful way to use this color is to selectively hide value labels. Currently, in Looker, the value label setting operates on all value labels; it’s not possible to apply labels to specific series. Fortunately, it’s very easy to work around this by using the transparent color in the ‘Series Colors’ option. (Sneak Peek! We will soon be introducing additional visualization settings that will make …

I don’t think this would be easily doable— You could move to a new axis, but then the scale of the column would still be skinny. That’s a neat idea though, and could be something to take into account if we make this more native functionality.

The workaround doesn't work when you have a stacked bar graph.

Please add this feature!

https://portal.feedback.us.pendo.io/app/#/case/17644

Top Labels in this Space
Top Solution Authors