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

[ismail1]

Knowledge Drop

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.

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.