Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Row Totals on separate Y Axis

  • 27 November 2018
  • 7 replies
  • 681 views
Paul_Wadsworth
Userlevel 2

I have a column chart, I would like to show the total, which on the data is easily provided by ticking the Row Totals box.

But on the chart this column is obviously far larger than any of the other columns. I can easily change this to a line from a column by finding the Row Total in the series options of the chart. But I want it to obviously be on a separate Y Axis so it doesn’t dwarf the other columns.


I think a lot of information I’m finding is a bit out of date, but in the options it looks like I can move the entire Series to a second Y Axis, but not the Row Totals individually.


Is there an option I’m missing? At the moment I’ve got a line that tops out at 1300+ and columns that often barely hit 50, so they’re difficult to read

7 replies

gavin.wims
Rank
Userlevel 1

Hi Paul,


Thanks for you query. If I understand correctly, you are talking about a situation where you have pivoted data. Have you considered the possibility of duplicating the pivoted row totals by extracting and summing the values using ‘pivot_index’? This would allow you to create a new series for each of those totals - you can then move these series to another axis with its own scale. I’ve attached an image to try to illustrate this approach.


Best,

Gavin.


Discourse

Paul_Wadsworth
Userlevel 2

Cheers for this.

The data is pivoted so this solution works. But where you have “Cancelled” and “Complete” I have dates. This means for my first attempt where I’m looking at a month I needed to add +Pivot_Index all the way up to 31, and if I wanted to look at 2 months I’d need to make sure it goes up to at least 62.


So while I’m hoping for something a bit more elegant than this, your solution does indeed work for me thanks.

izzy
Userlevel 7
Badge +1

I think a more ‘elegant’ way to do this could be to just say


sum(pivot_row(${count}))

in a table calc (assuming the field you want to sum is ${count}).


This’ll add up every pivot column in the row, not just the ones you manually specify. You could throw in a coalesce() a la Gavin’s solution if you need to to prevent nulls. Does that seem more reasonable for this case?

Paul_Wadsworth
Userlevel 2

Thanks, this gives me exactly what I was wanting.

izzy
Userlevel 7
Badge +1

Glad to hear it! There’s usually more than one solution to a Looker puzzle

Noa

Hi -- I have a follow-up to this question; rather than a column chart, I want a “stacked percentage” column chart. When I use this method, my total column ends up stacked with the rest, rather than treated separately. Is there a way to fix this?

 

Thanks!

D

@Noa  - agreed- this would be really handy! 

Reply


Terms of Service