agg

The AGG (aggregate) function is used to aggregate numbers from different organizations. You can choose which organizations to aggregate, independent of the organization structure.

Syntax

AGG('[node1]/[node2]',org1,org2,org3)

Use

Use this function when you want to sum values from several suborganizations or use values from organizations other than the one the KPI belongs to. Aggregating functions can also be used to define drill structure.

How it works

All values for each node for each selected organizations are calculated before the AGG expression is calculated.

How to create

In the calculator, click the AGG button. A new calculator window opens. Create the function by defining the desired Time function, double clicking the desired node 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: AGG('[node1]/[node2]', Organization1)

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

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

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

Example: AGG('[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: AGG('[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:

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

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

AGG('[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'