Knowledge Drop

# Sorting Row-limited Table calculation based on few Pivot values (Workaround)

• Looker Staff
• 0 replies

Last tested: Sep 15, 2020

# The Problem

There are some specific scenarios when you can’t sort a table calculation:

1. Calculations that hit a row limit, as described here.
2. Sorting a dimension or measure after you’ve already sorted by a table calculation, as described here.
3. Sorting a table calculation that makes use of an offset, as described here.

# A Solution that could work for some uses cases

The workaround involves the use of filtered measures to replace the pivoted dimension and associated measures

This is the conversion to make use of filtered measures (I kept the sorting based on the dimension to show the values of the calculation are the same)

### The equivalent table based on a filtered measure with the desired sort available on the measure and matching the table calculation values & sorting

` `

`# mySQL Database used was used here`

`# Adjust the sql logic to suit your sql dialect`

`dimension: is_last_year {`

`type: yesno`

`sql: \${created_year} = YEAR(DATE_ADD(current_date(), INTERVAL -1 YEAR)) ;; #logic for order date is last year`

`}`

`dimension: is_2_years_ago{`

`type:yesno`

`sql: \${created_year} = YEAR(DATE_ADD(current_date(), INTERVAL -2 YEAR)) ;; #logic for order date is 2 years ago`

`}`

`measure: order_last_year {`

`# hidden: yes`

`type: count #or avg, etc`

`filters: [is_last_year: "yes"]`

`}`

`measure: order_2_years_ago {`

`# hidden: yes`

`type: count #or avg, etc`

`filters: [is_2_years_ago: "yes"]`

`}`

`measure: order_change {`

`type: number`

`sql: (\${order_last_year}/ NULLIF(\${order_2_years_ago},0))-1;;`

`value_format_name: percent_2`

`}`

This content is subject to limited support.