Sorting Data in your Visualization

Introduction

With Holistics sort, you can put your data in alphabetical / numerical order to present it in a more meaningful way.

How to sort

There are two ways to sort:

• Using the sorting function in Visualization Settings
• Using the sorting function when viewing the reports on the dashboard

1. Using the sorting function in Visualization Settings

To use the sorting function in visualization settings, click on the drop-down field below the Sort section and select the fields you want to sort by.

You can use the sorting function to sort your bar/line/column chart, or use the hamburger menu () on the top right corner to sort.

Note: When you save the report, all the sort settings will be saved as well.

2. Using the sorting function when viewing the reports on the dashboard

Holistics allows viewers to quickly sort tables and pivot tables when viewing the dashboard by two ways:

• Click on the Down arrow (⌄) next to the column's name. You can sort as many columns as you like. The sorted column will have an arrow icon and a number (indicating the sort order) next to its name.
• Click on the Sort icon on the upper right, then choose the column(s) you want to sort from the drop-down. You can change the sort order by dragging and dropping the field names up and down.

Note: The sorting function on the dashboard only provides a temporary view for each viewer. It will be reset to the sort settings in visualization settings once the dashboard is refreshed.

Sort mechanism

Visualization types that support Sort

• Tabular visualization:

  • Table
  • Pivot table

• Non-tabular visualization:

  • Area chart
  • Line chart
  • Column chart
  • Bar chart
  • Combination chart
  • Pie chart
  • Pyramid chart
  • Funnel chart

Sort and row limit execution order

By default, in both tabular and non-tabular visualizations, the sort will be applied AFTER row limit is applied

If you want to override the default behavior i.e having the sort be applied BEFORE row limit, please reach out to us at [email protected]. At this time, only the sort-limit order in tabular visualizations can be overridden.

Therefore, after being overridden:

• Tabular visualizations: The sort is applied before row limit
• Non-tabular visualizations: The sort is applied after row limit (same as before)

Note: If you are sorting a business calculation field, the sort will be applied after the row limit is applied.

Explanation for the default sort-limit order

By default, Holistics limits the number of rows by adding a LIMIT clause to the query that is sent to the database. After that, when you use the sorting function in Visualization Settings, the sort is applied directly to the chart without adding an ORDER BY clause to the generated SQL.

This also explains the caution in Limiting data section of the docs.

Sort direction

• Ascending order (text from A to Z, capital letter before lowercase letter), number from smallest to largest, and date from oldest to newest).
• Descending order (text from Z to A, lowercase letter to capital letter), number from smallest to largest, and date from oldest to newest).

Sort fields

You can sort by one or many fields at the same time. The fields must exist in Visualization Settings in order to be sorted.

Note: If your chart is using Date dimension in the X-axis, you can only sort by that dimension.

Sort order

The sort order determines the sequence the data is sorted. For example: you want to sort your Revenue by category and country table by Country (ascending), then Category (descending). The returned results will display all the countries whose first letter goes from A to Z. Then, for the same country, all the categories whose first letter goes from Z to A will be displayed.

The vertical order of the fields in the Sort section in Visualization Settings indicates the sort order applied in the underlying query. In particular, the field with the upper position will be sorted first.

FAQs

Can I use ORDER BY clause in Query Model to override the default row-limit order?

No, you cannot use ORDER BY in Query Model to override the default row-limit order.

The reason is because when Holistics build Query Model, SQL query inside the model is wrapped as a subquery. Depending on the database logic, ORDER BY clause in a subquery may not be respected in the outer query, which could lead to an unexpected result.

Can I find the top/bottom N items using the sorting function?

In Holistics, we have a clear distinction between top/bottom N and first/last N:

• Top/bottom N items, based on a value, return the list of items with the values in the top N. There might be more than N items because some might share the same highest values.
  • Example: Top 10 athletes by score might return 12 names because the top three athletes share the same score (i.e. 10, 10, 10, 9, 8...)
• First/last N items based on a value return the list of exactly N items with the highest/lowest values. There might be some items whose values belong to the top/bottom N but they will not be included.
  • Example: First 10 athletes by score return exactly 10 names. Assuming that A and B share the same low score, but only A is included in the list while B is not because A is the 10th name and B is the 11th name.

If you want to find the top/bottom N items as defined above, we would recommend using the Top/Bottom N feature.

If you want to find the first/last N items, we would recommend using the sorting function with row limit in tables and pivot tables only. For non-tabular visualizations, the sort is applied after the row limit, therefore the final result is the first/last N items of the partial dataset, which is unlikely the correct result.