Single value chart from pivoted data

  • 13 June 2017
  • 6 replies

Hi Looker,

Would be awesome if we could use the Single Value Chart with pivoted data. Right now the following error is shown when trying to do this.

Here’s what my data looks like. I’m using table calculations with pivot_where’s to calculate a final “status”, which I’d like to visualize as a single value chart. Any chance this could be suggested to your product team?

6 replies

Userlevel 2

Hey Chris,

At the moment we cannot use single value viz with pivoted data. However we might be able to find a workaround for your specific case. If you can give us more details on

  1. Which table calc value exactly are you planning to use for Single Viz?

  2. What formulas are using for calculated fields - can yo you give us all?

If you can email that to we would be happy to work with you on it.

Can you share the gist of the workaround for this? I want the Single Value visualization from pivoted data as well.

Userlevel 3

@Pritul_Patel, as Aleks explained, we can’t currently create a single value vis with pivoted data. The workaround in the meantime would be to unpivot the date field and use different functions in the table calcs. For example, if we have data in this format which we cannot visualize as a single value because it includes a pivot:

We can unpivot the week column and calculate the same metrics with different table calculations so that we can display the results as a single value:

If you’re having trouble converting your particular calculations after removing the pivot, feel free to paste them here and we can help out. Or if you would prefer not to share them publicly you can shoot an email to with the specific examples.

Thank you for the hint, @rufus.

I was able to get the single value visualization by removing the dimension from pivot and adding it as second column. Then I used Table Calculations to get exactly what I wanted (coupled with SHIFT+Sort on second column). It is not a robust method and the visualization could be easily showing incorrect value when columns are sorted differently. Works for now.

What if in this case I also want to show comparison (of the previous month)?

The work-around is only applicable for a single underlying grouping variable.

Great News!

This feature is coming soon!

We expect it to be released to customer instances with Looker 5.12. If you can’t wait to see it in action, you can log in to Looker Next to play with it and see how it works.