Aggregating Data

Display different data aggregations within views

From version 2.5, most Omniscope View Toolbars include an Aggregation drop-down dialog which enables you to define an aggregated transformation of the underlying granular data set, and also define the aggregation functions to be applied across the aggregated values in each field (column). Each view on any given tab can use a different aggregated view as defined by the settings on the View Toolbar Aggregation drop-down.

Note: Formula columns can be set to apply the defined logic to the aggregated, rather than individual, row values by choosing 'Apply formula' as the aggregation function for the formula field.

Defining Aggregated Views

Consider the unaggregated data set illustrated below and downloadable here. Potential 'Aggregation Field 1' has 7 unique values, and potential 'Aggregation Field 2' has an additional 4 values uncorrelated with the 7 values in 'Aggregation Field 1'. In addition, there are Category, Numeric and Time & Date fields in the data set:

Use the Aggregation drop down menu found on most View Toolbars to define view-specific aggregated transformations of the underlying data set. The example above shows how to customise the drop-down fields and use the Preview window. In this case, the uniform value field 'Source' is being hidden, since aggregating by a single value will collapse the entire data set into one row. Notice that selections in the view are shown with brushing (fading) in the Preview window, enabling us to confirm that Value 1 is associated with both Second Value 1 and 2.

Ticking a field on the Aggregation drop-down tells Omniscope to aggregate by the unique values in the chosen field, apply the default aggregation function to the values in the other fields, and display a view-level mini-barometer upper right with the aggregated record count. In this case, the result is 7 aggregated rows, one for each of the unique values, with the 'Values-Numbers' summed and the average (mean) of the 7 aggregated 'Values-Dates'  displayed, since those are the default aggregation functions. Notice that the row headers now display how many individual records (rows) have been aggregated into each aggregated row. Aggregation functions are applied according to the defaults, but these can be changed for each field (or set to None) using the drop-down menus at right. Hover your mouse over each possible aggregation function (the list will depend on the data typing assigned to each field) and the tooltip will provide a definition of the function to be applied to that filed when aggregated. You can also change the default for a given field.

Aggregating by multiple fields will usually increase the resulting row count, since all unique combinations of field values from all fields will become aggregate rows. IN the example below, the data set is being aggregated by both Field 1 and Field 2, resulting in 12 rows as shown in the mini-barometer:

In this case, the Category column was duplicated and two different aggregation functions applied; unique value count (which counts how many different colours are in each aggregated row), and unique values list (which lists all the unique values in the underlying rows). The aggregation function for 'Numbers was changed to Mean (average), which shows the original value of 10, which will stay the same no matter how many rows are aggregated, since the value was constant in the unaggregated data. The aggregation function for Dates has been set to None, which results in the Dates filed being hidden in the aggregated view.

Aggregation vs Table View: Grouping option

Many Views include an Aggregation drop-down menu, used to define aggregated views of the data, together with the function to be applied when aggregating each field (column). Aggregation differs from the presentational Grouping options available only in the Table View. Unlike Grouping, the end user is not meant to 'unroll' or 'drill down' defined aggregations to lower levels of granularity. User-interactive Grouping can be used in the Table View in addition to Aggregation. For example, a file with each row defined as an observation for a given day can be aggregated by week, then grouped by month and year. In the resulting presentational Table View, an end user would be able to unroll the groupings down to weeks, but to see the maximum granularity daily data, they would either have to remove the Aggregation settings (which they cannot do in a free Viewer) or just click to the 'Data Set tab (which they can do in a free Viewer).