Last tested: Jul 13, 2018
Say you want to see some metrics for your top 10 customers by number of users, where the customer dimension is pivoted. Currently there is no easy way from an explore to sort a pivoted dimension based on measure values—you can only sort the pivoted dimension alphanumerically. Some features requests have popped up related to this issue like:
There are a few workarounds you can use.
This is the approach used in a couple of help centre articles:
This is the most robust solution, but it requires some work to set up the query for the derived table.
This workaround can only be used with a table visualisation and can only support up to 200 rows in the results, but can be done entirely from the explore page with no LookML changes.
This is a janky workaround that can only be used when there is no unpivoted dimension selected. It requires a small change to the LookML, but it is easy to implement and does not require a derived table.
In the pivoted dimension definition, add order_by_field: measure_name
. This will cause the pivoted dimension to be sorted by the measure values for that pivoted dimension, allowing you to simply sort the pivoted dimension to show the top N values.
As the warning in the order_by_field doc notes, however, there should be a 1:1 relationship between a dimension and the field referenced by the order_by_field parameter. Referencing a measure field in the order_by_field
parameter in a dimension definition can have unexpected results if there are any other dimensions in the query, since the dimension values will be sorted by the measure's overall values rather than the values for a particular group.
This content is subject to limited support.
Ok workarounds but a real shame this isn't a built in feature. The more time you spend jumping through hoops is less time you spend actually analyzing the data.