AGGR
is very useful for advanced aggregations. However, we need to be careful when using it in charts that have a Group dimension; either cycle or drill.
In this recipe, we will see how to overcome this using a Dollar Expansion.
This example follows along from the previous section. Load the data and create the straight table with the AGGR
expression.
Follow these steps to create a dynamic AGGR
expression for a group dimension:
In Document Properties | Groups, create a group called
Cycle1
with ProdID and Month as the dimensions:Edit the straight table and add
Cycle1
as the second dimension.Save the changes and look at the chart. Note that the expressions do not calculate correctly for all the different cycle values.
Open the properties of the table and edit the Sales $ expression:
Sum( AGGR(Sum(Price*Qty)/ExRate, Country, [$(=GetCurrentField(Cycle1))]) )
Click on OK to save the chart:
Click...