aggavg

The AGGAVG (aggregate average) function is used to find the aggregated average for several organizations. You can choose which organizations to aggregate, independent of the organization structure.

Syntax

AGGAVG('[node1]',org1,org2,org4)

Use

Use where there is no data at the top organizational level or when you want to retrieve data from several organizations. AGGAVG can also be used to define a drill structure.

How it works

All values for the node or node expression are first added together and then divided by the number of organizations that are included in the formula.

An organization will be included when the average is calculated, even if it does not have values.

If a calculation that uses AGGAVG is a fraction, the fraction will be calculated for each organization BEFORE the average is calculated. This means that AGGAVG will not give a weighted average.

How to create

In the calculator, click the AGGAVG button. A new calculator window opens. Now create the function by creating the desired Time function, double clicking the desired nodes and clicking or entering numbers and operators. Click OK when finished.

Next you must specify the organizations from which figures are to be included.

Click the desired organization on the right and the desired symbol in the middle. You can choose between the following:
- Add only current - Include values from the selected organization only

Example: AGGAVG('[node1]/[node2]', Organization1)

- Add children only - Include values from suborganizations of the selected organization only

Example: AGGAVG('[node1]/[node2]', +Organization1)

- Add current and children - Include values from the selected organization and all suborganizations

Example: AGGAVG('[node1]/[node2]', *Organization1)

To always use values from the KPI's organization (current organization) mark the Include 'this' checkbox and select Include only 'this', Include children only (+this), or Include 'this' and children (*this):

Example: AGGAVG('[node1]/[node2]',this)

To use values from all organizations on one or more levels, i.e. all children of the same parent organization, mark the Include parent checkbox and select Include only 'this', Include children only (+parent), or Include 'this' and children (*parent):
- Level - specify the number of levels to traverse

Examples:

AGGAVG('[100]',parent) or AGGAVG('[100]',parent1) to access parent of current context

AGGAVG('[100]',parent2) to access grandparent of current context

AGGAVG('[100]',parent5) to access great-great-great-great-grandparent of current context

Now click OK. The Calculation field will contain the completed function.

Renaming in functions

You can use 'AS' in functions that contains "organization", e.g. AGG, AGGAVG, IND, as well as TOP functions to give functions different names when displayed in drills.

Examples:

You want to sum up two nodes that say something about "Good management". This function will rename the IND function in drills to "Good management", and you will see it when you click on an organization name in the drill:

AGG('IND('[Just manager]+[Visible manager]') AS 'Good management'',organization)

This function will rename the AGG function in drills to "Good management":

AGG('IND('[Just manager]+[ Visible manager]')',100) AS 'Good management'

In order to show the average of the AGGAVG function you need to display the AGGAVG row. The other rows just show the value for each organisation. AGGAVG works differently from INDEX, in that respect.

The AGGAVG line is automatically hidden if there is only one organisation or if there is no AS keyword in the AGGAVG function. In order to show the aggregation line you can include AS in the function:

AGGAVG('[100]',*100) as 'AGGAVG'