How to build Aggregate blocks for a Dimension

When you are working with larger lists it is often necessary to define groups of elements into a single aggregated element that contains the totals for a group of elements in a single dimension. See for example below where we group countries by their starting letters.

Solution:

To achieve this, we first create a list of the required element groups from a single dimension, in the above example all countries starting with A and all countries starting with B as separate list groups. Once the groups are defined, we can then create aggregate lists that combine the group into a single aggregated value set.

Step 1: Create the list of elements from a dimension.

• Open Formulate and select the List option
• Add a standard list
• Select the dimension to be used
• Select the elements that make up the required group
• Save the list

Repeat for additional list groups. i.e. Countries starting with A and then countries starting with B

Step 2: Create Aggregate lists.

• Open Formulate and select the Formula option.
• Add an Aggregate Block.
• For the “Pick a List” option select the standard list created in step 1 by selecting the dimension then the “Fx” option to open the custom list selection pane.
• Save the new Aggregate block.

Note we do not use the Pick a Data Point option, this leaves the aggregate block to use any measures in the model.

 Step 3: Create a Parameter for the new Aggregate blocks (Optional).

• Open Formulate and select the Parameter option.
• Select the Model for Parameter Type.
• As a parameter type select the aggregate blocks to include in the parameter
• Save the new Parameter

Note you can also use the Element selection option in a discover to use the aggregate blocks, but a parameter will allow you to have a filter or slicer option in visualizations.

Results

The example to the right shows the use of aggregate lists and parameters to show values for predefined lists of countries as well as aggregated values for a specified list of countries.

The Orange Highlighted items show how to access the prebuilt lists and aggregate items
Right click on the dimension “Country” and select “View Elements”
Once the elements pane opens you can access custom content by clicking on the “Fx” icon.

The Green highlighted items show the use of a “Countries A” list, selecting this in Elements will show all countries starting with A as individual row items.

The Red highlighted items show aggregated lists where the list of countries are aggregated as a single row dimension. A parameter has been created in this case to enable the user to select from a drop down the aggregated lists they wish to see.
 

If you have Questions about this Article, please refer to it in a Question in our Q&A Section.

For feedback reach out to: community@pyramidanalytics.com