Understanding pivot table sorting and how it differs from table widget sorting
Summary: Vova Shenhofer explains why pivot table sorting may appear unordered, highlighting that sorting only applies within groups defined by the row hierarchy. For effective sorting, the field to be ordered should be the leading dimension. If a simple list is needed, a table widget is recommended as it sorts across all rows without grouping. This is a common query in Sisense for Linux, and the behavior is by design, not a defect. Testing in specific environments is advised for optimal application.
Introduction:
Sorted a pivot column but the values still look unordered? The is a good chance that sort is actually working as intended — pivots sort within row groups, not across the whole widget. Learn why this happens and how to get the order you expect. Applies to Sisense Linux, both Cloud and on-prem.
Understand why pivot sorting looks like it "isn't working"
A pivot table is an aggregating widget. It first arranges its rows by the hierarchy of dimensions in the Rows panel, and a sort is then applied within the lowest level of that grouping — not across every row of the widget. The sort is being applied; it just happens inside each group, so at a glance the column can still look unordered.
The rule to remember: in a pivot, sorting only applies to the last level of granularity — the groups within the last row dimension.
See it in a worked example
Consider a pivot with Contract ID as the leading row dimension, followed by Title, Supplier, Status, and an Expiration Date column, with an ascending sort set on Expiration Date — yet the dates still look unordered.
Because Contract ID is the leading dimension and is unique for every contract, each Expiration Date lands in its own single-row group. The ascending sort has no sibling rows to reorder, so the dates keep following the row hierarchy above them. The field type and the ascending setting are both correct — there is simply nothing to sort within each group. If there were multiple members in a group, you would see the sorting applied inside the group.
Sort by the field at the top of the hierarchy
Move the field you want ordered (e.g. Expiration Date) to be the first dimension in the Rows panel, then apply the ascending or descending sort. Sorting always respects the leading dimension, so the values order as expected across the widget.
Reasoning tip for a secondary column: if you keep Expiration Date as the second dimension after Contract ID, it will sort as a whole only if each Contract ID has exactly one Expiration Date. As soon as any Contract ID has more than one date, the sort on that second column applies inside each group instead.
Consider a table widget for flat, listing-style reports
If you want a flat list rather than a grouped summary, a table widget may be the better fit. Unlike a pivot, a table has no row hierarchy, so it sorts across all rows at once — an ascending sort on any column orders the entire list, regardless of which column you pick or where it sits.
The key differences to keep in mind:
Pivot table — an aggregating widget grouped by the row hierarchy. A sort affects only the rows within the last level of granularity, so column position matters and a sort respects the leading row dimension. Best for grouped, subtotalled summaries.
Table widget — a flat list of rows with no grouping. A sort affects all rows in the widget, so column position doesn't matter and any column sorts the whole list. Best for flat, listing-style reports.
So if the goal is simply to list rows in date order, switching to a table widget avoids the grouping behavior entirely and sorts the full list chronologically.
Conclusion:
Pivot sorting works as designed — it orders values within the row hierarchy rather than across the entire widget, so a sort on a lower-level column can look like it did nothing. To sort a value across the whole widget, promote that field to the top of the Rows panel, or switch to a table widget for a flat report. This is expected behavior, not a defect.
References/Related Content:
Pivot tables (Sisense documentation): https://docs.sisense.com/main/SisenseLinux/pivot.htm
Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this, please let us know.
