Question

How to assign table calculation column to right axis on graph

  • 28 March 2019
  • 2 replies
  • 1267 views

Hi All,



I am having issue plotting line chart with the x axis and y axis from the columns I want.



I am having a explore with two table (A and 😎 left joint together based on ‘as-of-date’ and ‘CBSA’ columns.



I created a few derived columns with the following steps:







  1. ‘Median Value’ created based on median of a column in table A group by ‘as-of-date’







  2. ‘Index A’ is created as a table calculation column based on top of ‘Median Value’







  3. ‘Index B’ is created from a table calculation column based on a column ‘Hpi’ from table B









the visualization table looks like:




Now I want to plot line graph with ‘as-of-date’ as x-axis and compare two lines: Index A vs. Index B.



however the line chart looks like this now:





I am thinking what should I do to achieve something like:




Could you please help me with it?



Thank you!


2 replies

Userlevel 7
Badge +1

The core issue here is with the way that Dimensions and Measures work in visualizations. “Median Value” is a measure, and Index A is based off of that which makes it also a Measure, so it plots as a series on the Y axis.



HPI is a dimension, so it plots on the X axis on the visualization. Index B is based off of that, so it is also a dimension as far as the visualization is concerned. This means it plots on the X axis (you can see how the labels include both the date and the Index B value.



The trick here is to do something a bit weird.



Let’s say Index B’s calculation is ${hpi}*1.5. If you change it to ${hpi}*1.5+(${median_value}*0), it will consider it a measure and chart it properly. You’re referencing median value, so it will turn that table calc into a “measure”, but since you’ve multiplied median value by 0, you aren’t changing the result of your calculation at all.



Does that make sense? I think something that’s on our radar at Looker is to make it easier to choose whether you want your table calcs to be dimensions or measures. 🙂

Thank you soooo much ! It is really helpful !!!

Reply