When table calculations cannot be sorted
Sorting on a table calculation works similarly to sorting on a dimension or measure in Centricity. However, there are two important differences that prevent sorting in some scenarios:
Table calculations are created after the data is retrieved from your database, which means that when you sort a table calculation, you can only sort the data that is already displayed.
Some table calculations are applied to multiple rows within the same column (for example, when using an
offset()function). In these cases, sorting the table calculation would change its results and is therefore disabled.
The specific scenarios when you can’t sort a table calculation are:
Calculations that hit a row limit, as described below.
Sorting a dimension or measure after you’ve already sorted by a table calculation, as described below.
Sorting a table calculation that makes use of an offset, as described below.
Calculations that hit a row limit
If the number of rows in your query exceeds the row limit that you’ve set, you will not be able to sort table calculations. This is because table calculations are only based on the rows that are displayed. Therefore, if you hit a row limit, the table calculation might be missing some rows that it should be sorting into your results. If you run into this issue, you can try increasing your row limit (up to 5,000 rows).
For example, a table may display the 10 top-selling categories in an e-commerce store, sorted by total sales. If a 10-row limit has been set and reached, you’re warned by the yellow bar displayed at the top of the table. In this case, you’ll be unable to sort the table calculation. However, you could show the top 10 categories by number of orders instead, meaning that you can sort by both measures included, even when you can’t sort by the table calculation.
Sorting a dimension or measure after sorting a table calculation
As indicated above, table calculations are only based on the rows that are displayed. In contrast, sorting by a dimension measure goes back to your database to make sure it finds the correct rows. As a result, you should start sorting with dimensions and measures. Then, when the correct data has been returned from your database, you can sort those results based on a table calculation.
Calculations using an offset function cannot be sorted
Any table calculation that makes use of an offset cannot be sorted, because the sort order of the rows would change the results of the offset. For example, a table calculation that displays the percentage change in weekly sales for the Accessories category cannot be sorted because the rows are ordered by date. The table calculation showing that weekly percent change is dependent on the rows being in chronological order, thus the calculation cannot be sorted.
Previous: Creating and sorting table calculations