ThoughtSpot aggregation formulas - user guide

Introduction

Aggregation is core to the process of finding insights in your data. You can use aggregation to organize your data at a level of detail that suits your business needs. For example, when looking at a spreadsheet that contains all sales data for your company, you may find it difficult to glean any useful information. Once you aggregate sales to sales-person or region level of detail, interesting insights emerge.

When ThoughtSpot 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.

ThoughtSpot 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, region, 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()

  • count(), count_if()

  • max(), max_if()

  • median()

  • min(), min_if()

  • percentile()

  • stdev(), stdev_if()

  • sum(), sum_if()

  • unique_count(), unique_count_if()

  • variance(), variance_if()

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()

  • cumulative_max()

  • cumulative_min()

  • cumulative_sum()

  • moving_average()

  • moving_max()

  • moving_min()

  • moving_sum()

  • percentile()

  • rank()

  • rank_percentile()

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 salesperson to revenue for the whole region. In order to see your results, create a table where the level of detail is salesperson, and use a group aggregate function to compute revenue by region. Your table shows one row per salesperson, but the "region revenue" column will always display the revenue for that salesperson’s region.

The group aggregate functions are

  • group_aggregate()

  • group_average()

  • group_count()

  • group_max()

  • group_min()

  • group_stddev()

  • group_sum()

  • group_unique_count()

  • group_variance()

In the preceding documentation and in the documentation that follows, all references to "table" refer to the table visualization. I.E., they refer to the table displayed by ThoughtSpot, not the underlying data in your model.

Related information:

Was this page helpful?
×