Aggregation formulas in ASK BOSCO®

Aggregation formulas - user guide

Introduction

When looking at a spreadsheet that contains all sales data for your website, for example, you may find it difficult to glean any useful information. Once you aggregate sales to Channel or Campaign level detail, interesting insights emerge.

When ASK BOSCO® aggregates your measures, it uses the aggregation specified in the worksheet by default. However, as you dive into your data, you will want to create formulas that are targeted to your own business use cases.

ASK BOSCO® provides a robust set of aggregation functions that you can use to customize your aggregations. These are grouped into three categories:

Basic aggregation

Basic aggregation functions aggregate a column to the level of detail specified by the search.

For example, when you specify that you want to show "average" revenue, that average computes at whatever level of aggregation you specify in your search. You may specify month, channel, or even no aggregation at all, in which case your results will show the average revenue for your whole table.

The basic aggregation functions are

average(), average_if() - compute average values, optionally with a condition
count(), count_if() - count rows, optionally with a condition
max(), max_if() - return the highest value
median() - get the middle value
min(), min_if() - return the lowest value
percentile() - show a value at a specified percentile
stddev(), stddev_if() - measure variability
sum(), sum_if() - add up values
unique_count(), unique_count_if() - count distinct values
variance(), variance_if() - measure spread in data

Table aggregation

Table aggregation functions perform calculations on the data that results from your search. These functions look across multiple rows of data in your table to compute a new value.

For example, cumulative functions fall into this category because they accumulate across multiple rows in a table. Similarly, rank and rank_percentile fall into this category because they assign a rank to a value based on how it compares to the same column value in other rows in the table.

The table aggregate functions are

cumulative_average() - calculates a running average over the ordered rows in your table
cumulative_max() - tracks the highest value seen so far as the table progresses
cumulative_min() - tracks the lowest value seen so far across rows
cumulative_sum() - adds up values row by row, showing a running total
moving_average() - calculates the average value over a rolling window of rows
moving_max() - finds the maximum value in a sliding window across rows
moving_min() - returns the minimum value within a moving window of rows
moving_sum() - adds values within a defined moving window (e.g., last 4 weeks)
percentile() - returns the percentile rank of a value withing the result set or partition, depending on how it's applied
rank() - assigns a rank to each row based on the order of a measure, either across the full table or partitioned group
rank_percentile() - returns a normalized percentile rank (0-1) for each row based on its position relative to others

Group aggregation

These functions aggregate to a specific level of detail that may differ from the level of detail specified by your search.

For example, you might want to create a table that compares revenue by campaign to revenue for the channel. In order to see your results, create a table where the level of detail is campaign, and use a group aggregate function to compute revenue by channel. Your table shows one row per campaign, but the "channel revenue" column will always display the revenue for that channel.

The group aggregate functions are

group_aggregate() - applies a custom aggregation expression over a defined group, different from the table's level
group_average() - returns the average for a higher-level group (e.g., average revenue per channel in a campaign-level table)
group_count() - returns the count of rows for each higher-level group, even if the table shows more granular data
group_max() - returns the maximum value within each group, regardless of table granularity
group_min() - returns the minimum value within each group, regardless of table granularity
group_stddev() - calculates the standard deviation of values within a broader group
group_sum() - returns the total sum of values for a higher-level group (e.g., total revenue per region when showing city-level rows)
group_unique_count() - counts distinct values within a broader group than what's shown in the table
group_variance() - returns the variance of values within a higher level group

In our documentation, all references to "table" refer to the table visualization. I.E., they refer to the table displayed by ASK BOSCO, not the underlying data in your model.