Row Totals on separate Y Axis


(Paul Wadsworth) #1

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


(gavin.wims) #2

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.


(Paul Wadsworth) #3

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) #4

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) #5

Thanks, this gives me exactly what I was wanting.


(Izzy) #6

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