Formula Window

dlg_formula

With predefined formulas you can perform calculations by using manually entered or imported values. Element values can be entered manually (i.e. typed in), imported or derived from a formula. In general, lower-level elements (sub-elements) are given values manually or by import. While upper-level elements (compilation elements) derive their values from formulas. These formulas take the values of the sub-elements as inputs for calculating their results.

The element value calculated on the basis of the formula is immediately shown on the Indicator of the Display tab. For example, if you calculate an average of two sub-elements with the values of 50 and 60, the compilation element indicator displays the value 55.

Furthermore, values for selected series and selected periods can be recalculated in the Value tab.

Defining a Formula

To define a formula for a series of an element:

1.	In the Element Properties window, select the Series tab.

2.	In the Input Type drop-down list, select Formula.

3.	In the case the previous input type was not formula, the Formula window opens. Otherwise click the "Define..." button to open the formula editor.

4.	If the series has a default formula defined in the Series Properties window, you can either use the default formula or define your own formula by selecting the Customized option.

5.	To define a formula, activate the formula text field and place the cursor into a position where you wish to add a function. Then use the Function menu button to enter functions. The selected item will appear at the cursor location.

There are only the five most common functions available in the Function menu button. To toggle a full list of functions, select More Functions... from the Function menu button. The Functions list will open to the right side of the window, and you can insert functions to the cursor position either by double-clicking the desired function or by highlighting the desired function and clicking the Add to Formula button. You can narrow down the list of functions by selecting the desired category from the drop-down menu above the function list.

8.	To enter parameters for a function you have inserted in the text field, place the cursor inside the function's parentheses and select element/series identifier from the Elements and Series menu button. The selected item will appear at the cursor location.

To toggle a full list of elements/series, select More Elements... from the Element and Series menu button. You can search for elements in the element list by using the search functionality below the element list. The Elements list will open to the bottom of the window, and you can insert elements to the cursor position either by double-clicking the desired element or by highlighting the desired element and clicking the Add to Formula button.

10.	You can sort and search the list of elements with the functions provided in the elements section.

•Show names shows only the names of elements, series and scorecards on the list.

•Show identifiers shows only the identifiers of elements, series and scorecards on the list.

•Show both shows both names and identifiers on the list.

•The Show all series checkbox defines whether there are all series visible in the list. If the checkbox is not checked, only the default series is shown for all the elements.

•To search for a certain scorecard, element, or series, type the search word to the field on the left side of the Search button.

•You can limit the search matches with the Case sensitive and Exact match options. The Case sensitive option makes the search look for a match which is typed exactly like the search term. For example, with the search term Time in the Dentorex example model, you would get the On-time Delivery element as the result with a regular search, but with the Case sensitive option turned on it would not be found anymore because the word time is written with a lowercase t in the word On-time. The Exact match option searches for whole name or identifier phrases from the model, i.e. you would need to type "on-time delivery" to the search field to find the On-time Delivery element when the Exact match option is turned on.

dlg_formula_full

11.	If you are comfortable with entering the formula straight into the text field, you can do so. Just activate the text field and enter the formula.

12.	Make certain that the formula is valid. In a valid formula, the identifiers of the sub-elements are within parentheses and separated by commas, for example AVERAGE(SC1.MEA11.ACT(),SC1.MEA12ACT()). You can also use numbers, for example, AVERAGE(SC1.MEA11.ACT()1.1,SC1.MEA12.ACT()0.9).

13.	Click the OK button to save the formula into the database.

Tip: You can use wildcards with the scorecard, measure, and series identifiers.

Formula Window				Formula_Window Dear Support Staff,
Formula Window