Group By is analogous to Excel’s pivot tables. It groups multiple rows of data into a single one based on a list of columns. A common use scenario is aggregation from item-level data to policy-level, such as item written premium, item changes in loss reserves, etc.
Common choices for aggregators
- Deductible: Max
- Item-based premium: Sum
- Item-based limit: Sum
- Names (line, agency, etc): Alphabetical max
- Earned premium: Last
- Inforce premium: Last
- Claims ending balances: Last
The First aggregator will select the first value in the grouped set of rows. It is often necessary to do an Order By before using first to guarantee that the data is ordered appropriately before using it. If all the values in a column are the same within a grouped set of rows, then first is the most appropriate choice.
The Last aggregator will select the last value in the grouped set of rows. It is often necessary to do an Order By before using last to guarantee the data is ordered appropriately before using it.
The Concatenate aggregator combines all the values into one cell.
The Sum aggregator will sum all the values in a grouped set of rows. If the underlying data is text, then BriteData will attempt to convert the data to numeric form before calcultating the sum.
The Minimum aggregator will return the smallest value within the group.
The Maximum aggregator will return the largest value within the group.
Aggregators: Alphabetical Minimum
The Alphabetical Minimum is much like Minimum, except the order will be based alphabetically instead of numerically.
Aggregators: Alphabetical Maximum
The Alphabetical Maximum is much like Maximum except that the order will be based on the alphabet instead of numbers.
The Count aggregator returns the number of rows within the grouped set. Use Count to get policy counts, claims counts, etc.
The Unique aggregator returns any unique values in the grouped set, separated by commas.
Aggregators: First Filled
The First Filled aggregator will start from the top of the grouped set and work its way down, returning the first filled value that it finds. It’s often useful to use this in conjunction with Order By (for example,Item Sort Order) to be certain that the value returned is the value that you intended.
This aggregator is available only for Summary Groups and it removes the column from the summary worksheet only.