List of workarounds for sorting pivots by measure / sorting by column totals / dynamic top n ranking

[molly_lippsett]

Knowledge Drop

Last tested: Jul 13, 2018

The problem

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:

• Sort by Row / Sort pivots by value
• Rank top N / Pivots sortable by measures
• Sort or limit pivot columns by top values

There are a few workarounds you can use.

Workaround #1: Derived table with rank window function

This is the approach used in a couple of help centre articles:

• Sort by Pivots, Rank with Other Bucket, and Percentile Tail Analysis
• Dynamic Rankings (Filtering Dimensions by Custom Rank)

This is the most robust solution, but it requires some work to set up the query for the derived table.

 

Workaround #2: Sort row totals and transpose

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.

1. Select the dimension you want pivoted as an unpivoted dimension, and the dimension you want unpivoted as a pivoted dimension.
2. Check the Row Totals box.
3. Sort on the Row Totals column. If your query is hitting the row limit, you can use an extremely janky workaround:
   - Move the Row Totals column to the left using the arrow to the right of the Row Totals checkbox
   - Sort on the second measure column (the one immediately to the right of the row totals column)
   - Column to the left (row totals column) will be sorted instead
4. Transpose table in the vis options.

 

Workaround #3: order_by_field

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.                

[chriswragge]

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.