Calculations in Analyses

The Analyser in Accelerus allows you to set up analyses of your database. When setting up an analysis, three of the Analysis operators allow you to define formula expressions: the Filter, Formula and Aggregate operators.

Accelerus analyses allow more than the extraction of the raw data currently in the database. Instead, source data can be worked upon - filtered, aggregated, ranked, etc - with additional fields created to store calculated data. In so doing, formulas may need to be defined, and the Calculation Editor is used for this purpose.

For example, below, in a Formula operator, two new fields have been created named CountD and CountE. Each contains a formula using the DCountIf function, whereby the number of results equal to D and E respectively are being counted and stored in these new, temporary fields. Each formula may have been typed directly into the Formula expressions table of the operator, or the Browse button clicked to bring up the Calculation Editor in which they were setup.

RA FormulaCalcBrowseButton

In the Analyser, three of the intermediary or advanced operators, the Filter, Formula and Aggregate operators require the setup of user defined formulae. To do so, they use a common window, the Calculation Editor. This allows a formula to be built, while drawing upon available fields, functions and operators.

RA CalculationEditorLabels

Feature

The formula is composed in the top pane. It may be typed directly into the pane, or available fields, functions and operators from the panes below may be inserted into it. The formula must have the correct syntax for the functions used. White space, including line feeds, in the formula may be used to make it easier to read and these are ignored when the formula is run.

The relative size of each of the panes may be changed by clicking and dragging the resizers between them.

The Fields pane contains a tree of all of the available fields for insertion in calculations. These are grouped in folders by entity type and, within each grouping, there may be further subgroupings of fields, until the fields are displayed.

The available fields will vary, depending on:

•	The entity type, eg whether results, entrolments or one of the student entity types is being used.

•	Where the operator is positioned in the analysis and whether earlier operators have constricted or expanded the data or not.

•	If user defined fields have been created in upstream operators.

It is recommended that available fields are double clicked to insert them into the formula box, to ensure that they are spelt correctly, surrounded by braces, etc, rather than typing them.

A wide range of functions is available, with the required syntax displayed beside each, eg AvgX(expr, expr, ...).

The functions are organised within folders, eg Mathematical, String etc. Each of the functions in the Calculation Editor, together with its syntax and example usage, may be accessed by clicking here.

Functions may be typed or double clicked to insert them into the formula pane.

The right most pane contains all of the operators that may be used when building a calculation, eg =, >, OR, &, etc. These are also grouped in folders, based on their type, eg Mathematical, Logical, etc.

These may be typed in or inserted from the Operators pane by double clicking.

RA CalcEditorFunctionDesc When a specific item is clicked in the Fields, Functions and Operators panes, a brief description of the item is displayed below the three panes.

For example, here the AnyTrue function is highlighted and a description informs you of its usage.

Pressing F1 in the Calculation Editor will bring up on line help from which more detailed information is available about each field, function and operator.

The three panes in the bottom part of the Calculation Editor all contain hierarchical folder structures from which you may draw fields, functions and operators. The following may be used to navigate and make selections from any of these panes:

•	Open and close a folder to reveal the available fields, functions or operators by:

➢	Clicking the arrowhead beside the folder to open and close respectively.

➢	Double clicking the folder or its text.

➢	Using à and ß keys to open and close folders.

•	Use á and â to move up and down through the folders and and their fields, functions and operators.

•	Insert a highlighted field, function or operator into the calculation pane by:

➢	Double clicking it.

➢	Pressing Enter.

➢	Pressing the space bar.

Once you have activated the Calculation Editor window using the method appropriate to each of the Filter, Formula and Aggregate operators, you may define an expression using the correct syntax:

•	Typing the formula directly into the formula box at the top of the Calculation Editor, ensuring the correct syntax, function names and arguments, operators and available fields are used.

•	Compose the formula by inserting its various components from the Fields, Functions and Operators panes, ensuring that the mouse pointer is positioned in the formula box at the point you wish to insert the element:

➢	Insert the required field from the Fields pane, by either double clicking the field or pressing Enter or the space bar when it is highlighted.

The field will be inserted in the formula box with a set of curly brackets around it, eg {St.Family}.

➢	Insert the appropriate function from the Functions pane, in the required position, by either double clicking it or pressing Enter or the space bar when it is highlighted.

The function will be inserted with a set of parentheses following the function name, eg DCountIf( ). The mouse pointer will be positioned within these parentheses and you must type in the correct arguments for the selected function.

➢	Insert an operator from the Operators pane, in the required position, by either double clicking it or pressing Enter or the space bar when it is highlighted.

•	Ensure the formula has the correct syntax, matching sets of parentheses, etc.

•	Ensure that the formula has been composed so that its components are evaluated in the correct order required by you, ie in accordance with the precedence rules.

•	Click the OK button.

•	When back in the Analyser window, if an operator contains an error, indicated by the red exclamation mark in the Steps pane:

➢	Click the error symbol.

RA OperatorErrorIcon

A message box will appear, informing you of the reason for the invalidity of the operator.

RA CalcErrorMsg

➢	You must click open the invalid operator’s details and correct the error therein, before you can run the analysis.

Analysing data

The Analysis window

The Analysis Explorer

The Calculation Editor