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.
For this recipe, we will reuse the data load for the Using nested aggregation recipe from ealier in this chapter.
- Drag a
Table
object. - Add the text,
Europe Rank
, as the object title. - Add a dimension:
Country
. - Uncheck the
Include null values
property. - Add a new measure with the following expression, with the
Regional Rank
label:
= Max( Aggr(rank(Sum( {<Region={'Europe'}>} Sales)), Country))
- Add the second measure with the following...