To create expressions of calculated facts, special functions can be used:
Syntax:
SUM(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns an accumulated sum of the F fact by the L1 level within the L2 accumulation period. An accumulation period is a level, within which accumulation is made. Accumulation starts over with the start of each accumulation period. If a string is specified as an expression, the function returns 0.
Examples:
SUM[@[1],MONTH,YEAR]
returns the accumulated sum of the fact with the @[1] identifier by the MONTH level within a year.
SUM[@[1],MONTH,ROOT]
returns the accumulated sum of the fact with the @[1] identifier within the whole calendar.
Syntax:
FIRST(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns value of the first element of the F fact by the level of the L1 dimension within the L2 period.
Example:
FIRST[@[1],QUART,YEAR]
returns the value of the first element of the fact with the @[1] identifier by the QUARTER level within a year.
Syntax:
FILL(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns value of the last non-empty element of the F fact by the level of the L1 dimension within the L2 accumulation period.
Example:
FILL[@[1],QUART,YEAR]
returns the last non-empty value of the fact with the @[1] identifier by the QUARTER level within a year.
Syntax:
DIF(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns the difference between the current and the previous value of the F fact by the L1 level within the L2 subtraction period.
Example:
DIF[@[1],MONTH,YEAR]
returns the difference between the current and the previous values of the fact with the @[1] identifier by the MONTH level within a year.
Syntax:
LAST(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns value of the last element of the F fact by the level of the L1 dimension within the L2 period.
Example:
LAST[@[1],QUART,YEAR]
returns the value of the last element of the fact with the @[1] identifier by the QUARTER level within a year.
Syntax:
SUM_S(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns an accumulated sum of the F fact by the L1 level within the L2 accumulation period. This function works for the selected dimension elements only.
Syntax:
FIRST_S(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns value of the first element of the F fact by the level of the L1 dimension within the L2 period. This function works for the selected dimension elements only.
Syntax:
FILL_S(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns value of the last non-empty element of the F fact by the level of the L1 dimension within the L2 accumulation period. This function works for the selected dimension elements only.
Syntax:
DIF_S(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns the difference between the current and the previous values of the F fact by the L1 level within the L2 period. This function works for the selected dimension elements only.
Syntax:
LAST_S(F, L1, L2)
where:
F. Fact.
L1 and L2. Level names.
Description:
The function returns value of the last element of the F fact by the level of the L1 dimension within the L2 fact. This function works for the selected dimension elements only.
Only the levels of a calendar dictionary should be used as the levels in these functions. The following string constants are used to specify them:
DAY. Day.
WEEK. Week.
MONTH. Month.
QUART. Quarter.
HYEAR. Half-year.
NINEMONTH. Nine months.
YEAR. Year.
ROOT. Root.
See also: