The Custom Group action lets you group and aggregate data based on a custom set of fields or calculations. This is useful when the type of thing you want to group by does not have its own table in the database.

For example, the perspective below shows data from three joined tables Courses, Sections, and Meetings. Between Courses and Sections, we have sandwiched a Custom Group that shows building codes and, for each building, the sum of durations of meetings in that building:

There is no “Buildings” table in this example. Instead, we used a formula to derive a building code from the first five letters of each Room Number in the Meetings table.

The Custom Group action is also frequently used with single-table datasets, e.g. to select fields to group by for aggregation. For example, we can group labor statistics by state, and calculate average salaries across relevant data points:

In the structure of a perspective, a Custom Group is a subquery that takes its data from inward-referencing formulas, rather than from an associated data table. Often, the formula is just a simple reference to the field to group by, with no actual calculations involved.

How to Use

In an existing perspective, select the field(s) you want to group by, and click Custom Group in the context menu. Then select the level where the grouping should occur.

A Custom Group subquery can be placed within any existing one-to-many relationship above the referenced fields. If only one location is possible, it will be used automatically.

The new subquery appears with the name Custom Group in grey text in the table header. Each field in the new subquery contains a formula, pulling data to group by from nested subqueries.

Grouping and Aggregation

In a Custom Croup subquery, you can add formulas with aggregate functions such as sum and count. There is a shortcut action for some of these.

Continuing our example, we could find the total Capacity for each Room Number. We use the shortcut Sum action, and select the Custom Group subquery as the desired level:

When the number of distinct grouped-by values is small, a crosstab visualization can be useful.

Grouping by a Calculation

You can edit the formula that defines the grouping. This allows you to group by the results of a calculation. Most commonly, you might wrap the existing reference in some function.

Continuing the previous example, we might take the first five letters of each Room Number to construct a building code, using the left function. Notice how both the grouping and the result of the adjacent aggregate formula changes as the formula is modified:

Note that grouped-by and aggregated values may be sourced from different subqueries. See above with e.g. Room Number and Capacity in Meetings and Sections, respectively.

For grouping purposes, a null value is enumerated as a distinct value, after other values. Null values may occur in the data itself, or arise when there are no records at a referenced location. Both cases are illustrated below:

Null values can be filtered out if desired, as in any other subquery.

For date and time fields, functions such as year and month can be used to group by relevant periods in the calendar. The next example will illustrate this.

Multiple Custom Group Subqueries

A perspective can have any number of Custom Group subquery, in any combination.

For example, below we use the year and monthName functions in separate nested subqueries, to group Log Events by year and then by month:

Custom Group subqueries can exist in independent branches of a perspective. The following example shows two one-to-many relationships in parallel, with a Custom Group on each.

Grouping by Multiple Fields

Alternatively, you can select multiple fields for grouping in a single Custom Group. This will enumerate all combinations of values that occur together.

You can hide specific fields in the Custom Group subquery to temporarily omit them from the grouping. If a field is sorted on, it will be included in the grouping even when hidden.

Grouped-by fields may come from different subqueries. For example, we can group Sections by Format and Classroom Type from the Sections and Room Data tables, respectively:

Deleting a Custom Group

To delete a Custom Group subquery, right-click Custom Group in the table header, and click Delete Custom Group.

Subqueries below the deleted one will be moved up, preserving their existing field selections, filters, format options, and so on.

The Delete Custom Group action also appears e.g. when deleting the last group-by field.

When to Use

The Custom Group feature is most appropriate when you wish to group by a subset of fields in a table, rather than by the overall entity that is represented by each row.

For example, if we have tables called Courses and Meetings, and we wish to find the total meeting time for each course, then there is no need for a Custom Group. Instead, we use the Courses table as the starting point for the perspective, and add the aggregation there:

By contrast, if we see that many courses have been repeated from year to year, and we wish to find the total historical meeting time across courses with the same title, then we would use a Custom Group on the Title field.

Another use for the Custom Group action is to group data in a different order than would be dictated by join relationships in the database. For example, we can show Sections by Instructors by Courses, even though Instructors is not joined directly to Courses:

In this case we are grouping by Instructor ID, which ends up being displayed as a compact join.

Custom Group vs. Move to Root

The Custom Group and Move to Root actions both affect the hierarchy of subqueries within a perspective. The two actions differ as follows:

• Custom Group inserts fields to be grouped by as formulas in a new subquery, between or above existing subqueries. The relative order of existing subqueries is unchanged.
• Move to Root reorders existing subqueries relative to each other.