Juletip #2 - How to calculate AVG, MIN, MAX, etc… for aggregated measures in VA?

[PetriRoine]

Have you ever seen the error message “Nested aggregated expressions are not supported”? Well, I have. When you are doing a little bit more advanced New Calculated Items in VA you might bump into this road blocker and wonder how to get around it.

The problem is that VA doesn't currently allow nested aggregated expressions i.e. when you are creating a New Calculated Item you can’t use Aggregated measure inside an Aggregate operator (see dummy example below).

However, you can often use alternative approach to get things done, namely using an aggregated data source.

VA 8.3 introduced capability to create an aggregated data source which allows us to create aggregated measures without using aggregate operator. Looking at the picture above you could f.ex. create Population (AVG) by using Aggregated data source instead of using New Calculated Item and Aggregate Operator. Population (AVG) created this ways allows you to use it inside Aggregate Operator.

!!! Note that Aggregated Data Sources support Aggregated (simple) and Aggregated (advanced) operators, but not the Aggregated (periodic) operators.

How to create aggregated data source

1. In the Data pane, select a data source.
2. Click Action icon and then select New data from aggregation of data-source. The New Aggregated Data window is displayed.
3. Select the data items to add to the aggregated data source in the Available items list. Then, click +> to move it to the Selected items list.
4. (Optional) Change the name of the aggregated data source in the Name field.
5. Click Ok. The new aggregated data source is available in the Data pane.

Now your new data source has aggregated measures of which aggregation you can define normally to be SUM|AVG|MIN|MAX|etc... and also utilize inside Aggregate Operators when creating new calculated items.

You may utilize your new measures directly by utilizing the new aggregated data source or by joining the aggregated measures to your original table.