Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

How to assign table calculation column to right axis on graph

  • 28 March 2019
  • 2 replies
  • 1359 views
Maggie_Chen

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




image


the visualization table looks like:

image


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:


image


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

image


Could you please help me with it?


Thank you!

2 replies

Maggie_Chen

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

izzy
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. 🙂

Reply


Terms of Service