How to assign table calculation column to right axis on graph

(Maggie Chen) #1

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 B) 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!


(Izzy) #2

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. :slight_smile:


(Maggie Chen) #4

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

1 Like