I have two items that I want to compare on a few different metrics. Unfortunately, the Table look seems to only allow the pivot dimension on top of the measures. While I understand how this limitation may seem logical, it makes it quite difficult to compare the pivoted columns, which somewhat defeats the purpose of pivoting in the table.
This is my data table in BigQuery
Date
Item
Metric 1
Metric 2
Metric 3
January
A
9
33
200
January
B
100
66
400
February
A
9
33
200
February
B
100
66
400
March
A
9
33
200
March
B
100
66
400
I can load this in Looker and easily pivot this to:
A
B
Date
1
2
3
1
2
3
February
9
33
200
100
66
400
January
9
33
200
100
66
400
March
9
33
200
100
66
400
But what I really want to do is compare A and B, not 1 2 and 3 within A:
1
2
3
Date
A
B
A
B
A
B
February
9
100
33
66
200
400
January
9
100
33
66
200
400
March
9
100
33
66
200
400
However, this layout of the table seems impossible in Looker without completely restructuring the data into long/tidy format and then doing a complicated join. I know how to do this in Excel/Sheets and Tableau, so I am surprised it’s so difficult in Looker.
Is there a way to show the third table from the first table? The second table is very user unfriendly for this use. I am using Looker Core.
Isn’t this just changing the pivot order? Seems like in second table you pivoted first table by Item first and then pivoted by Date. If you did the opposite order does that not work for you?
You can’t “just change” the pivot order as far as I can tell with tables in Looker Core. I agree it’s a simple change in concept.
It’s also not two pivots. It’s one pivot. You pivot “Item”, while “Date” remains as an unpivoted dimension and Metrics are measures. The metrics aren’t dimensions and can’t be pivoted like that.
What you’re describing only works if you do a long/tidy table for your dataset:
Date
Item
Metric
Value
January
A
1
9
January
B
1
100
January
A
2
33
January
B
2
66
January
A
3
200
January
B
3
400
February
A
1
9
February
B
1
100
February
A
2
33
February
B
2
66
February
A
3
200
February
B
3
400
March
A
1
9
March
B
1
100
March
A
2
33
March
B
2
66
March
A
3
200
March
B
3
400
But that’s not how the data is set up and that’s not how BigQuery works. The data is set up in the tabular form in the first table, so you have Metric 1, Metric 2, Metric 3 as columns, not Metric/Value.
To get from tabular to long format would require a transformation and a join and some development work to make sure it all integrates nicely. This is doable but much more intensive than it should be. So I was hoping to find a way to use the tabular data (since that’s how most data is shaped and how BigQuery works).
Would maybe native derived tables work? The measures will be treated as dimensions in the derived table, the new creator is just a click path and it would rely on the original LookML.
interesting, thank you. i will definitely explore this. it’s not as simple or elegant as i was hoping but this may be our best bet to show the data in a way that’s most useful for our customer