Using Level-Aware Aggregations - Amazon QuickSight

Using Level-Aware Aggregations

You can choose when to compute your aggregation in relation to display-level aggregations in a query. In addition to the regular filters and calculations, you can also add level-aware aggregations (LAA). Level-aware aggregations are available in both Standard and Enterprise editions, in all supported AWS Regions.

Without using any level-aware aggregations, you influence the timing of your filters and aggregations simply by where you place them, as follows:

  1. Before you import or access data with Amazon QuickSight, you can filter and aggregate the source tables. If you are using a direct query, you can also edit the SQL—adding WHERE clauses, joins, and even aggregations—before the data becomes part of a data set. Amazon QuickSight treats all new data the same way, and doesn't detect any of your transformative prework.

  2. Before you start an analysis, you can prepare your data at the data set level by creating filters. You can also add simple calculations, format dates, and define hierarchies. In addition, if you choose you can add row-level security.

    Data that is filtered out of the data set is completely unavailable to anyone creating an analysis on this data. However, these filters run again each time the data is refreshed.

  3. Inside an analysis, you can add aggregations, calculations, and more filters. You can apply the filters either to the entire analysis or only to parts of it, for example to specific visuals. You can also create table calculations such as windowing functions. In specific types of visuals, you can also add totals and subtotals.

Using these standard methods, you create a customized view of your data. Amazon QuickSight builds a query based on the combination of settings in each visual in an analysis, the analysis itself, and the data set behind it. Usually this is all you need to answer your analytical questions. But in some cases, you need more options.

Using level-aware aggregations (LAA), you can inject calculations at a specific point in Amazon QuickSight's process to evaluate and build queries. With LAA options, you can create calculations that run at the prefilter or preaggregate level. These help you to answer the following types of questions:

  • How many of my customers made only 1 purchase order? Or 10? Or 50? We want the visual to use the count as a dimension rather than a metric in the visual.

  • What are the total sales per market segment for customers whose lifetime spend is greater than $100,000? The visual should only show the market segment and the total sales for each.

  • How much is the contribution of each industry to the entire company’s profit (percent of total)? We want to be able to filter the visual to show some of the industries, and how they contribute to the total sales for the displayed industries. However, we also want to see each industry's percent of total sales for the entire company (including industries that are filtered out).

  • What are the total sales in each category as compared to the industry average? The industry average should include all of the categories, even after filtering.

  • How are my customers grouped into cumulative spending ranges? We want to use the grouping as a dimension rather than a metric.

For more complex questions, you can inject a calculation or filter before Amazon QuickSight gets to a specific point in its evaluation of your settings. To directly influence your results, you add a calculation level keyword to a table calculation. For more information on how Amazon QuickSight evaluates queries, see Order of Evaluation in Amazon QuickSight.

Supported calculation levels include the following:

  • PRE_FILTER – Before applying filters from the analysis, Amazon QuickSight evaluates prefilter calculations. Then it applies any filters that are configured on these prefilter calculations.

  • PRE_AGG – Before computing display-level aggregations, Amazon QuickSight performs preaggregate calculations. Then it applies any filters that are configured on these preaggregate calculations. This work happens before applying top and bottom N filters.

You can use the PRE_FILTER or PRE_AGG keyword as a parameter in the following table calculation functions. When you specify a calculation level, you use an unaggregated measure in the function. For example, you can use countOver({ORDER ID}, [{Customer ID}], PRE_AGG). By using PRE_AGG, you specify that the countOver executes at the preaggregate level.

By default, the first parameter for each function must be an aggregated measure. If you use either PRE_FILTER or PRE_AGG, you use a nonaggregated measure for the first parameter.

For level-aware aggregations, the visual aggregation defaults to MIN to eliminate duplicates. To change the aggregation, open the field's context (right-click) menu, and then choose a different aggregation.

For examples of when and how to use level-aware aggregations in real life scenarios, see the following post in the AWS Big Data Blog: Create advanced insights using Level Aware Aggregations in Amazon QuickSight.