[RETIRED]Creating Reference Lines for Charts through the Visualization Editor (3.18+)


(Abby West) #1

This article has been retired. The information is now in the Looker documentation on this page.


Options for setting target or comparison values in tables and graphs
Dashboard notes (3.16+)
Looker 3.18 Release Notes
Looker 3.50 Release Notes
(Anika Kuesters Smith) #2

If you leave the label parameter blank, the reference line will show the actual value:


(Brett Sauve) #3

You can also combine words with the actual value to create a label. For example …

Average: {{mean}}

… will show something like Average: 123.0

The following calculations can be put in the {{ }} syntax:

  • median
  • mean
  • max
  • min
  • deviation (represents the standard deviation)
  • variance

Looker 3.26 Release Notes
#4

As of version 3.26, reference lines will remain on the chart even when the series they are referring to is hidden. This is particularly useful for reference lines that have custom values!


Looker 3.24 Release Notes
(Elie) #5

Hello there!
Nice feature but I am trying to go beyond this example and I am hitting a wall. Thus three questions for the price of 1:

  1. I have a dataset with one attribute in pivot (by row I have dates and in pivot I have category and my measure is a count). How can I create a mean line for each of the 3 categories (in the reference line in LookML it states that reference lines only applies to the first series, but how about in the graphic editor?)
  2. How can I format the displayed value of the reference line (my number is in the 10,000s thus having the thousand separator would be nice :slight_smile: )
  3. The tricky one: if 1 is possible can it be applied to the Row Totals functions?
    Thank you guys.
    Elie

(Nicole Beyer) #6

Hey Elie,

  1. It is not currently possible to create a reference line that takes all of the pivots into account. I will definitely pass your use case along to product.
    I have personally worked around this in the past by creating a table calc. Specifically, I have used this article to manually calculate an average. The disadvantages of this workaround are that the line will not be straight and it is rather manual.

  2. This is not currently possible but I’m happy to pass the idea along to product for you!

  3. This is not currently possible but you could possibly accomplish something similar by using a table calc to get the row totals then iterating on that calc rather than the built in one.
    Note: You will almost certainly want to take advantage of the hide from visualization feature for this case.


Looker 3.30 Release Notes
#7

As of version 3.30, reference lines labels can now be formatted using value format!

When you create a reference line, enter the desired formatting in the Value Format box:

Read more about using value format here.


(Brayden) #8

It would be great if the label on the reference line value could be displayed on the left side of the chart.

Often a chart shows the most recent data on the right hand side, and the reference line value box can overlap the data making it hard to see the actuals.


(Zachary Michel) #9

Thanks for the feedback, Brayden. I’ll definitely let the Product team know about this small caveat of the feature.


(Arushi Bajaj) #10

Is there any way to add a weighted average as a reference line?


#11

Hi, I am also interested to know if it is possible to add a weighted average reference line. Is this possible?


#12

@Arushi_Bajaj and @Kim1233 the best way to create a weighted average reference line would be to create a table calculation that calculates a weighted average the way you want. For example, if you wanted to weight average user age by the count of users, you could add both user count and average age to an explore. Then, you could create a table calculation like this:

mean(
sum(${orders.count}*${users.average_age})
/
sum(${orders.count})
)

This table calculation will return a single value, the weighted average, for every row of the query.

Then you can make this table calc chart as a line using Series Types. Since the table calc only returns one value, the line will be a horizontal reference line. If you don’t want users count to appear in your chart, you can also hide that field using the Hide from Visualization option.

Alternatively, you can create a weighted average measure in LookML like this.


Making a Weighted Average Measure
(Whitney Kramer) #13

@lindsey - Similarly, I’d like to plot the upper and lower confidence intervals on a trend line over time. Based on your response to Arushi and Kim1233, the process is to plot a calculated measure (ie. add a column to the data table and plot that)?


#14

@Whitney_Kramer I would recommend doing this with a table calculation if possible, which you can create on-the-fly without adding any columns to your database. However, table calculations do not include statistical analysis, so you may have to determine the confidence intervals elsewhere if you don’t have that data already.


(Colin) #15

Just to add to Lindsey’s note - the table calcs do have standard deviation and mean and other simple stats. So things like confidence interval shouldn’t be too bad. Here’s a example I just made in learn: https://learn.looker.com/x/JQYvdns

Edit, if you want to get even more wild, can highlight outliers: https://learn.looker.com/x/bWb5kWB

We hope to have scheduling on thresholds like these late this year.


(Jillian Corkin) #16

Curious if there have been any updates to the label placement (or plans to update)? We’re running into the same issue where results in the graph are hidden underneath the label as it currently sits on the right.
Thanks!


(Colin) #17

Hi Jillian,

Definitely a feature we’d like to add, but a little trickier than it looks. We are constantly working on small features like that alongside bugs, so hopefully can find it’s way into a release in the next few months.

Colin


#18

@brayden @jcorkin for your visualizations where the reference line label is overlapping the data point, you can add some right padding in the visualization editing panel on the x-axis, which will move your last data point(s) out from under the reference line label:

Without Padding:

With Padding (40):


(Colin) #19

Just wanted to update everyone here that moving reference line labels to left, center, or right snuck in to 3.50, so folks should be seeing that as the release rolls out and Ryan’s hack will no longer be needed.


(Noa Flaherty) #20

Is there any way to create vertical reference lines along the X axis?