Chapter 8. Advanced Aggregation with AGGR

In this chapter, we will focus on understanding what the AGGR function is and how to use it. The following topics will be covered:

• Using nested aggregations
• Using Rank() with Aggr
• Combining set analysis with Aggr
• Creating an ABC analysis

Introduction

This chapter will focus on the use of the special function Aggr. In very specific cases, we need to calculate summaries over a group of dimensions.

Aggr is the only function that can enclose or be enclosed by another aggregation function.

Using nested aggregations

We know how to use several aggregation functions such as sum, max, min, and avg. When used in a measure of a chart or table, they aggregate values over the dimension stated in that chart or table.

Sometimes, you need to create a second aggregation based on the result of the first. An example could be the value and the name of the country with the most customers. This can be done using Aggr, combined with the Max() and Firstsortedvalue() functions.

Getting ready

Create a new Qlik Sense application, and load the following script that gives information on sales per country:

LOAD * INLINE [ 
Region, Country, No of Customers, Sales 
Americas, Uruguay, 200, 56000 
Europe, France, 900, 252000 
Americas, Brazil, 1500, 330000 
Europe, Croatia, 500, 160000 
Americas, Mexico, 1800, 122000 
Americas, Argentina, 1200, 360000 
Europe, Portugal, 1100, 302500 
Europe, Spain, 800, 250000 
Europe, Denmark, 1500, 435000 
Europe, Belgium, 700, 154000 
Asia, China, 2000, 370000 
Asia, Japan...

Using Rank() with Aggr

The Rank() function is likely used to calculate the relative position of the value on the dimension of the chart, starting from 1 to N.

As Rank() requires a dimension in the object to calculate the relative position, how can we use it in a text object to show the name of the top five countries with more sales?

This recipe will show you how to use Rank() with Aggr to get this top five visualization in a dimensionless object.

Getting ready

For this recipe, we will reuse the data load for the Using nestedaggregations recipe of this chapter.

How to do it…

1. Drag a Text & image object onto the content area.
2. Add the following text: Top 5 Customers, and type Enter to create a new line.
3. Format the text as bold.

4. Go to the property editor and open the Data section.
5. Add a new measure with the following expression:

=concat( 
    aggr( if (rank(sum(Sales)) <= 5, Country), Country)  ,  
    ',', 
    aggr(rank(sum(Sales)),Country) 
) 

The result will look like the following screenshot...

Combining set analysis with Aggr

This recipe will show you how to use set analysis with aggregation functions combined with Aggr().

Set analysis modifies the behavior of the filters in an aggregation expression. When using Aggr(), you have two aggregations, one inside Aggr() and the other outside Aggr(), as in the following example:

= Max( Aggr(rank(Sum(Sales)), Country)) 

Where to insert the set analysis expression? Will the result be the same?

This recipe will show how the set analysis in the inner or the outer aggregation affects the result.

Getting ready

For this recipe, we will reuse the data load for the Using nested aggregation recipe from ealier in this chapter.

How to do it…

1. Drag a Table object.
2. Add the text, Europe Rank, as the object title.
3. Add a dimension: Country.
4. Uncheck the Include null values property.
5. Add a new measure with the following expression, with the Regional Rank label:

= Max( Aggr(rank(Sum( {<Region={'Europe'}>} Sales)), Country))

6. Add the second measure with the following...

Creating an ABC analysis

ABC analysis divides a set of information, such as customers, products, suppliers, or any other dimension, into three categories: "A" for those contributing to the first 80% of an indicator, such as costs or sales, "B' for those contributing to the next 10%, and "C" for the final 10 %.

This recipe will show you how to create an ABC analysis coloring a dimension with this classification, and use the classification as a dimension, so you can filter only the countries that fall under the "B" category, for example.

Getting ready

For this recipe, we will reuse the data load for the Using nested aggregations recipe of this chapter.

How to do it…

1. Create a new Dimension in the Master items library.
2. In the dimension editor, select the expression editor for the field.
3. Add the following expression:

=Aggr( 
    If(Rangesum(Above(Sum(Sales)/Sum(Total Sales),1,RowNo()))<0.8, 'A', 
     If(Rangesum(Above(Sum(Sales)/Sum(Total Sales),1,RowNo()))<0.9, 'B', 
      'C')), 
    (Country...