A value reference in an expression can replace any function argument. For example, you can use value references instead of numbers in the SUM function, e.g. SUM(<reference1>, <reference2>) works just as well as SUM(<number1>, <number2>). The expression has a following syntax:

 

[<sc_identifier>.][<mea_identifier>.]<series>([<period>])

 

 

Note: Parameters inside the brackets are optional and can be omitted.

Value references marked with < and > signs should be replaced with the actual identifiers.

 

 

Scorecard Identifier - <sc_identifier>

Each scorecard has an unique identifier. To refer to a particular scorecard, syntax marking <sc_identifier> should be replaced with a scorecard identifier or, if you want to refer to a current scorecard, use qualifier _THIS  instead of the scorecard identifier. To refer to the parent scorecard, use qualifier _PARENT. In the case the scorecard identifier is omitted, the current scorecard (i.e. the one containing the formula) is used.

 

 

Tip: You can change the scorecard identifier through the Model Navigator frame of the Scorecard Explorer.

 

To change the scorecard identifier, do the following: In the Model Navigator frame, click the scorecard, the identifier of which you want to change, with the right mouse button. A pop-up menu will appear. Select Properties... from the menu and the Scorecard Properties window opens. To change the identifier, activate the text field Identifier and enter a new identifier. Click OK to save the settings.

 

 

Measure Identifier - <mea_identifier>

Similarly as scorecards, every measure (element) has an unique identifier. The measure identifier defined in this parameter is looked for in the scorecard defined in the <sc_identifier> parameter, and in the case the scorecard identifier parameter has not been defined, the identifier is looked for within the elements of the current scorecard (i.e. the scorecard containing the formula).

 

To refer to a particular measure, syntax marking <mea_identifier> should be replaced with a measure identifier, or if you want to refer to a current measure, use qualifier _THIS. To refer to the parent measure, use qualifier _PARENT.

 

In the case measure identifier is omitted, the current measure is used in the formula.

 

 

Series Identifier - <series>

Marking <series> represents an unique series identifier. Replace it with the existing series identifier or if you want to refer to a default series, use qualifier _DEFAULT.

 

 

Period Identifier - <period>

To refer to a period, syntax marking <period> should be replaced with either the qualifiers ALL, ALLTODATE, ALLTOPERIOD, LATEST, LATESTTODATE, LATESTENDED, or an offset value (positive or negative), or it should be left empty. If the period name is omitted, current period will be used.

 

Offset is an integer value relative to the period that is being calculated. As an example, if the period is April, the offset value of -3 refers to January. As indicated in the previous example, you can also calculate results for past periods as well as for future periods. To calculate results for past periods, you must add a minus sign to the formula as well as the number of periods you want to subtract from the current period. In this way, -1 refers to the previous period, -2 the one before that, etc. You can calculate results from any past period, even if it is 100 periods back.

 

In addition, multiple periods (including past, current and future periods) of the same element/series combination can be referenced also with the following syntax:

•Range of periods, e.g. 5 periods before and 1 period after the current period: [-5..1] (note that the smaller period value needs to be given first).

•Individual periods, e.g. 5, 3, 1, and 0 periods before the current period: [-5 -3 -1 0] (note that no commas should be used to separate the values).

 

Examples:

The following formula will calculate the average of the past three periods:

 

AVERAGE(MEA1.ACTUAL(), MEA1.ACTUAL(-1), MEA1.ACTUAL(-2))

 

The formula above can also be written in both ways shown below:

 

AVERAGE(MEA1.ACTUAL([-2..0]))

 

AVERAGE(MEA1.ACTUAL([-2 -1 0]))

 

To refer to a latest period containing a value, use the qualifier latest. For example, if the current period is April but no value exists for this period, the value for the previous period March will be used. If there is no value for March, then the search continues backwards, one period at a time, until a period containing a value is found. The latesttodate qualifier behaves otherwise similarly as the "latest" qualifier, but all periods that start later than the date on the QPR Metrics Server are counted with null values. New periods are also calculated automatically when the latesttodate parameter is used, i.e. when the date advances so that a new period comes into use, the values are recalculated as there is one more period with actual values available. In addition, you can also use the latestended parameter, which behaves otherwise similarly as the latesttodate one, but it includes only periods that end before the date on the QPR Metrics Server. New periods are also calculated in a similar fashion as with the latesttodate parameter, i.e. when the date advances so that an ongoing period ends, the recently ended period is included in the calculations. All is used to refer to all periods of a particular series. Alltodate returns a list of all values up to the current date. Alltoperiod is used to refer to all periods of a particular series up until the current period.

 

 

Tip: You can use wildcard characters in scorecard, measure, and series identifiers for representing any character or any string. To learn more about wildcards, see Wildcards.

 

 

Additional Examples

For examples of different kinds of formulas, see Formula Examples.