Data Sorted, Insights Unlocked

With a lot of data on the screen, imposing some order becomes essential to get the right insights quickly. Therefore, the ability to sort the data as you need it becomes critical. Halo Reports not only provides natural and simple mechanisms to reorder the data in your table, but also supports more advanced scenarios, tailored specifically to the dynamic cross-tabulation use cases.

Very often, the queries produced in Halo Reports are relatively simple. One or two categories, one specific statistic - for example, percentages or means - are, in many cases, enough to get valuable insights about the market or industry. For such cases, Halo Reports allows you to quickly sort ascending or descending by the given statistic, by either selecting the row or column you want to use for sorting, or any of the data cells with the given statistic. With such a selection active, you can click on the sorting icon in the spreadsheet toolbar to immediately apply an ascending or descending sort, based on your selection. If you need to see brands sorted by product satisfaction rating, that’s the simplest way to achieve it.

If you add an additional dimension, like year, to the table, you can use the same shortcuts to sort by data from a selected year.

Note that if the data cell corresponds to a categorical dimension in both rows and columns, applying the sort with a specific cell selected will, by default, apply the sort top-to-bottom rather than left-to-right. For a horizontal sort, select the row you want to use as the basis for the sorting, or use the sort rule creator.

If you need a certain dimension ordered alphabetically, you can easily achieve it by clicking on any of the labels (e.g., brand name) and applying the default sort (ascending or descending). This will sort the data in the table based on the label of the response in the given dimension, rather than on the cell with a calculation result. 

You can see that whenever a sort is applied to the table, an additional icon appears next to the name of the dimension that was sorted. If you sorted brands by their satisfaction score, the indicator will appear next to the name of the question with brand responses, and you can see on hover the specific rule applied to change the order of brands in the table.

If your query includes nested variables, the same shortcuts are still applicable. You can pick a row or a column, or you can trigger the sort from within a selected cell. In such a case, it is important to understand that the sort will be applied separately within each nested category. For example, if you nest body types in engine types, showing the average satisfaction for each combination of engine + body type, the body types will be ordered based on their satisfaction score separately in each engine type.

This takes into account the specifics of data cross-tabulation rather than assuming this is just a plain table. In case you would need to sort the scores for all combinations of engine + body type, it can still be achieved. You only need to use the built-in user-defined question feature to create a custom question combining the engine and body type information, and then use it in your query instead of two separate questions.

Query definition in Halo Reports allows you to not only nest variables, but also to put them in parallel on the same level of nesting. You may be interested in reviewing several satisfaction ratings on brand, segment, and engine type levels within one query.

For such a table, each parallel (not nested) section of the query definition is considered a separate calculation branch. If you click on a specific cell within such a table (e.g., one within the segments data), the suggested quick sort will only apply to the selected branch (e.g., segments). If you select a whole row or column, the system will automatically apply a consistent rule across all calculation branches.

Having the rules stored and applied separately for each branch allows for defining more complex scenarios. If needed, Halo Reports allows you to have, e.g., segments sorted by one satisfaction item while having the engine type sorted by a different one.

In Halo Reports, the sorting applied is considered part of the definition of the query rather than a one-off action done independently. Thanks to this approach, any sorting rule applied can always be:

Let’s dive a bit deeper into the last point. Let’s assume your query shows brand data sorted by the satisfaction score. A sorting rule applicable to the brand question and defining the sort as driven by the specific satisfaction score is stored within this query definition. As long as the query definition does not change dramatically and the brand question and the relevant satisfaction metric remain present, the sorting rule will continue to apply. The sorting rules persist, among other scenarios, in the following situations: 

In order to be able to track and manage the sorting rules applied to the table, Halo Reports provides a dedicated rule editor for sorting. Even simple sort rules created by the shortcut described earlier can be previewed and modified in this editor. Let's revisit the example with multiple questions sorted separately. If you click on the dropdown next to the sorting icon in the toolbar, you will see all the rules applied within that query. For each of them, you can start editing it, or you can start creating a new rule.

If you start creating a new rule, a dedicated window will appear. As the first step, you need to decide which dimension you want to apply the sort to. Note that WHAT you sort is not equivalent to HOW you sort it. If you want to sort brands by the value of a specific KPI in 2020, you need to pick the brand question here, not the year, as it is the brand question that you want to display in a different order. Once you have picked the relevant dimension, in the next step, you decide whether to order the responses alphabetically or by the value in a specific row/column, and whether the sort should be ascending or descending. If you decide to sort by value, the interface will guide you through the query definition in the next step, letting you pick a specific row or column in a way that follows the query definition naturally. If the calculation branch to which the question belongs contains multiple other questions and multiple statistics, you will be guided to set “coordinates” pointing to one specific row or column.

Note that if your selection in the results spreadsheet is not allowing the tool to provide a valid “quick sort” suggestion, then clicking on the sorting icon will only allow you to edit or create new rules, without the ascending / descending shortcut.

One question from the query can only have one definition of sort assigned in a query. If you decide to sort brands by value in 2020, and then create a rule to sort by value in 2019, the new rule will overwrite the previous one. If you want to include multiple rules within one sorting definition, it is possible within the rule editor. You can add more rules to one sorting definition, and those rules will then be executed sequentially. This effectively means that the second (and following) rules will only be applicable if the previous rules result in an ex-equo position for some elements. The menu allows you to reorder the existing rules as well.

Looking at the examples below, we can see that the following rules have been added:

Makes of new car are sorted by the mean value of General quality in 2018 (rule 1), if several makes have the same value, then those makes are sorted alphabetically (rule 2).

There is a slight difference in the approach, depending on whether you start creating a new rule or editing an existing one. When you edit an existing rule, the first selector in the rule editor is fixed, allowing you to only work on that specific question. Only if you create a new rule can you choose the scope of the sort in the first step. Note that in parallel to using the sorting icon in the spreadsheet toolbar, you can also get to the rule editor directly from the query definition panel, via the context menu. It will behave similarly to the editing option, focusing only on the specific question and relevant sorting definition.

So far, we have focused on sorting categorical questions. Halo Reports sorting mechanisms can also support sorting of values coming from numeric questions. A good example may be a query that includes multiple numeric statistics, e.g., for various satisfaction ratings. By default, the numeric questions will always appear in the results in the order as manually defined in the query definition panel. A common need will be to display them in ascending or descending order based on the specific brand of interest. If the query includes numeric questions (e.g., mean or Top-2-Box ratings), the rule editor will expose an additional option “Numeric questions” in the first selector. Once selected, it will not allow sorting alphabetically, only by specific value (effectively, by specific row or column in the data). In our example, we can decide to sort the numeric questions based on the mean values for the Alpina brand.

You can see that as a result, the order of numeric questions has changed in the results spreadsheet. It is important to understand that this has not impacted the definition of the query - the questions in there are still in the order you defined originally. Such an approach has two specific benefits:

If numeric questions rule is applied, the additional indicator in the table will appear next to each of the affected numeric questions' labels.

In case of more complex queries, where numeric questions are mixed with other calculations (e.g., based on categorical variables), if the numeric questions rule is applied, it will aggregate all numeric questions with the chosen statistic and present them in the defined order, putting all other variables separately.

With nesting, the rules will behave similarly to the cases presented earlier - the sorting will apply separately within each nesting level.

It is possible that the query will comprise several types of numerical statistics (e.g., means and Top-2-Boxes), and there will be a need to have both subsets sorted accordingly. In such a case, the possibility to include multiple rules in one sorting definition can be used to achieve the required layout. One query can only contain one “Numeric questions” sorting definition, but within that definition, we can include separate rules for sorting means and Top-2-boxes. As a result, we will get the means grouped together and sorted, and the Top-2-boxes grouped and sorted separately, even if in the query definitions they were mixed with each other.

As mentioned earlier, the rule definitions are stored within the query. This means that any static sorting definition will always reapply in the same way. If we decide to sort data by 2020 results, it will always do it if 2020 is present in the data. If we decide to sort by values for Alpina, the rule will always check if results for Alpina exist and apply the sort based on them. The dynamic query definition features existing in Halo Reports will sometimes make the static rules not flexible enough from the perspective of the dynamic report definition. To support such cases, the rule editor allows you to pick “first” or “last response” rather than specifying a specific row or column that should be the basis for the sort. When selected, the sorting rule will always check what the first or last response available is and use it as the base for sorting. If the dynamic decisions within the report change what the response will be, the query will be resorted automatically. Let’s look into two sample use cases where this option may come in handy.

You are interested in the way certain values change over time. Therefore, your query includes the last 10 years of data, and your table splits the result by year. Within the analysis, you would like to have your table sorted by the results in the most recent year. That can be easily achieved with static sorting rules. However, you would like the report to be automatically relevant whenever a new wave of data becomes available. In order to set it up like this, you use the dynamic connection option, so that the query always uses the 10 most recent years, rather than years that were explicitly selected manually in the connection section.

Now, if the next wave of data becomes available, the list of years will move by one. If the sorting rule refers to a specific year in a static way, the moment the next year appears, the table will not be sorted by the last available, but by the previous one. In order to make it more flexible, you check the order of years in the table, see that the most recent is always the last available column, and you can change the rule to refer to the last available year in the table rather than a specific one. You have now created a report that automatically sorts the data by the most recent year, no matter which year it is. This will work perfectly also in the case where you want to have some additional dynamic flexibility in the data selection, for example, a parameter to select the country you want to analyze. Each country can have a different year available as the most recent. Using the “last response” option will make the query work as expected for each of them.

You prepared a dynamic report where report consumers can pick a specific brand of interest. The brand parameter subsets the list of brands in the data, so that only the selected brand appears. You would like the results to show up sorted for this brand. In the rule editor, you can only define a specific brand for the sorting rule; it will only work as expected if the parameter selection matches the static rule. Instead, you define the sort to pick the first available response. Since you always subset the list of brands to one selected by the parameter, it will always be the one selected by the report consumer that will effectively serve as the basis for the sorting.

If you make your reports accessible for others in Viewing or Storybuilding mode, the sorting options remain available in the Table Viewer. As the report author, you can define the starting point for the report, without limiting the insights mining potential of reordering the data if needed. All options will work as in the authoring environment, including the smart “quick” sort suggestions and the rule editor.

Query results can also serve as the basis for Compose content. In such a case, the sorting defined in the Query module affects how the data is presented in Compose. If the query table is directly placed on the Compose page, this component supports sorting the data further independently, already on the page. Compose charts, on the other hand, will always present the data in the exact same order as defined in the original Query, so any adjustments need to be made already there.

If you populate the Grid component with the Query data, it will reproduce the order from the source query. While there are no direct sorting options available in the Grid interface, you can use expressions to update the definitions of dynamic columns and rows, therefore impacting the order of the data displayed.