View the original community article here
Last tested: Sep 15, 2020
The Problem
There are some specific scenarios when you can’t sort a table calculation:
- Calculations that hit a row limit, as described here.
- Sorting a dimension or measure after you’ve already sorted by a table calculation, as described here.
- Sorting a table calculation that makes use of an offset, as described here.
The issue with sorting on a table calculation when a limit is reached
When there is no limit preventing the Table Calculation sort
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.