This summary provides overview of all formulas that can be used in reports or data sets (predefined indicators).
If you are searching for transformation scripting during data import, see Developer documentation.
Indicators and codes
Each dataset indicator is specified by its unique code starting with M_
(M as Measure). Accessing the calculated indicator's value is possible by typing this code directly into the formula. For example:
M_NAME_1 + M_NAME_2
Another way how to get the value of the indicator is to use the value()
function:
value('M_NAME_1') + value('M_NAME_2')
Strings must be always enclosed by apostrophes: 'L_NAME'
.
Both examples have the same result.
On this page:
Drill-down (members) aggregation
Members aggregation determines the way how to count values in the case that exists more records for one selected member in a single time unit. Aggregation type is specified by adding the appropriate suffix to the indicator's code. When not specified, the SUM
aggregation is applied.
Suffix |
Description |
Example |
---|---|---|
|
Calculates the sum of all values for the selected drill-down attribute |
|
|
Calculates the minimum of all values for the selected drill-down attribute |
|
|
Calculates the maximum of all values for the selected drill-down attribute |
|
|
Calculates the average of all values for the selected drill-down attribute |
|
|
Calculates the distinct count of all values for the selected drill-down attribute |
|
Date-Time aggregation
The date-time aggregation specifies the way, how to count values in the case that you display indicator values in higher time units (lower granularity data) than the time units in which are data stored in the system (higher granularity data).
Suffix |
Description |
Example |
---|---|---|
|
Calculates the sum of all values for the selected time interval |
|
|
Calculates the minimum of all values for the selected time interval |
|
|
Calculates the maximum of all values for the selected time interval |
|
|
Calculates the average of all values for the selected time interval |
|
|
Calculates the distinct count of all values for the selected time interval |
|
Drill-down and date-time aggregation
Both drill-down and date-time aggregation can be specified simultaneously.
M_NAME<at:var at:name="SUM" />SUMT
Both aggregation methods can be used in any other formulas when relevant.
Counting of level members
Each drill-down level is represented by particular members, for example the level City
contains members like Berlin
, Paris
, New York
etc. To get the count of these members, use the following syntax:
L_NAME_COUNT //L_NAME represents the level code (prefix L like level) L_NAME_DCOUNT //to get the unique count use the _DCOUNT suffix instead
Datetime functions
For the purpose of this reference guide: Date refers only to year, months, days (and quarters, weeks) and their combinations. Time refers to hours, minutes and seconds and their combinations. For combination of date and time we strictly use datetime term.
Date strings
The date string parameters are entered absolutely (dd.MM.yyyy or yyyy-MM-dd) or relatively (time variables) by operators:
date +|- n[d|BellaDati25en:w|m|q|y]
where
date
is date indd.MM.yyyy
oryyyy-MM-dd
format, or one ofactualyear
,actulamonth
,actualquarter
,actualweek
,actualday
,now
n
represents the count of:d
days,w
weeks,m
months,q
quarters ory
year.
Examples:
'2011-01-01 + 1q - 2m + 1d' means Jan 1, 2011 + 1 quartal - two months + 1 day, that represents Feb 2, 2011 'actualYear + 1m + 1d' , (used on 8 Mar, 2011) means 1 Feb, 2011 + 1 month + 1 day, that represents 2 Feb, 2011 as well
Another way how to create the date strings is following:
date(day: dd, month: MM, year: YYYY) date(d: dd, m: MM, y: YYYY) date(week: w, year: YYYY) date(quarter: q, year: YYYY)
Changing datetime context
What is the datetime context? Consider following example:
|
01/2011 |
02/2011 |
03/2011 |
formula indicator |
1000 |
1200 |
1300 |
Formula is evaluated for each column - in this example, in columns are values evaluated for particular months. During the evaluation of value 1000
, the datetime context was 01/2011
, then during the processing of value 1200
, the context was 02/2011
etc.
Function |
Description |
---|---|
|
Changes the context of the evaluated expression to dateAt('2011-01-01', { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; }); |
|
Changes the date context of the expression and evaluates it aggregated in the specified interval dateInterval('2011-01-01', '2011-12-31' { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; }); Values for the indicators |
|
Changes the context of the evaluated expression to timeAt('13:33:23', { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; }); |
|
Changes the time context of the expression and evaluates it aggregated in the specified interval timeInterval('00:00:01', '23:59:59' { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; }); Values for the indicators |
|
Changes the context of the evaluated expression to |
|
Changes the date context of the expression and evaluates it aggregated in the specified interval |
|
Changes the context of the evaluated expression to |
|
Changes the time context of the expression and evaluates it aggregated in the specified interval |
|
Evaluates the expression without date time interval. |
Obsolete functions
These functions may be removed in further releases
Function |
Description |
---|---|
|
Loads the indicator's value at specified date. |
|
Loads the indicator's value at specified date aggregated for N previous levels. |
|
Loads cumulative indicator's value for specified date interval. |
|
Loads cumulative indicator's value for specified date interval aggregated for N previous levels. |
Advanced functions
Function |
Description |
||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
This function gradually adds the current value to the cumulated value. Example:
|
||||||||||||
|
This function gradually adds the current value to the cumulated value. Example:
|
||||||||||||
|
Value of the passed indicator calculated for previous date or time value (e.g. previous month, day, hour, ...). Example:
|
||||||||||||
|
Value of the passed indicator calculated for previous date or time value (e.g. previous month, day, hour, ...). Parameter |
||||||||||||
|
Value of the passed indicator calculated for next date or time value (e.g. previous month, day, hour, ...). Example:
|
||||||||||||
|
Value of the passed indicator calculated for next date or time value (e.g. previous month, day, hour, ...). Parameter |
For changing the whole context of the evaluated expression, you can use following functions:
|
Changes the context of the expression to desired previous date period. The value of the
|
||||||||||||
|
Changes the context of the expression to desired next date period. The value of the |
The following example works with values, which are loaded for one year before the actual table/chart datetime entry:
return prev(YEAR) { M_NAME_1 / M_NAME_2 * 100 }
You may simply enter only the first letter of time unit (in case of time context changing formulas) instead of their full names (Y,Q,M,W,D), for example prev(Y){}
Nested expressions
Date and time functions can be combined (if applicable), eg.: Value of M_INDICATOR at 8th December 2010, 9:04:02AM:
dateAt ('2010-12-08') { timeAt('09:04:02') { M_INDICATOR } }
Working with datetime constants
Accessing context datetime
Function |
Description |
---|---|
|
Returns the formula's context day in |
|
Returns the beginning of the context's week in |
|
Returns the beginning of the context's month in |
|
Returns the beginning of the context's year in |
|
Returns the formula's context week start in |
|
Returns the day of year from the context date |
|
Returns the day of month from the context date |
|
Returns the day of week from the context date |
|
Returns the number of month from the context date |
|
Returns the number of month from the context date |
|
Returns the number of hour from the context time |
|
Returns the number of minute from the context time |
|
Returns the number of second from the context time |
|
Returns the number of days in the context date |
Examples:
|
31.1.2011 00:01:00 |
1.2.2011 00:01:00 |
2.2.2011 00:01:00 |
contextDay() |
31.1.2011 |
1.2.2011 |
2.2.2011 |
contextWeek() |
31.1.2011 |
31.1.2011 |
31.1.2011 |
contextMonth() |
1.1.2011 |
1.2.2011 |
1.2.2011 |
contextQuarter() |
1.1.2011 |
1.1.2011 |
1.1.2011 |
contextYear() |
1.1.2011 |
1.1.2011 |
1.1.2011 |
dateDayOfYear() |
31 |
32 |
33 |
dateDayOfMonth() |
31 |
1 |
2 |
dateDayOfWeek() |
1 |
2 |
3 |
dateMonth() |
1 |
2 |
2 |
dateYear() |
2011 |
2011 |
2011 |
timeHour() |
0 |
0 |
0 |
timeMinute() |
1 |
1 |
1 |
timeSecond() |
0 |
0 |
0 |
Accessing actual date
Function |
Description |
---|---|
|
Returns the actual day in |
|
Returns the actual week in |
|
Returns the actual month in |
|
Returns the actual quarter in |
|
Returns the actual year in |
Math functions
Function |
Description |
---|---|
|
Returns the absolute value of a double value. |
|
Returns the absolute value of a float value. |
|
Returns the absolute value of an int value. |
|
Returns the absolute value of a long value. |
|
Returns the arc cosine of a value; the returned angle is in the range 0.0 through pi. |
|
Returns the arc sine of a value; the returned angle is in the range -pi/2 through pi/2. |
|
Returns the arc tangent of a value; the returned angle is in the range -pi/2 through pi/2. |
|
Returns the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta). |
|
Returns the cube root of a double value. |
|
Returns the smallest (closest to negative infinity) double value that is greater than or equal to the argument and is equal to a mathematical integer. |
|
Returns the first floating-point argument with the sign of the second floating-point argument. |
|
Returns the first floating-point argument with the sign of the second floating-point argument. |
|
Returns the trigonometric cosine of an angle. |
|
Returns the hyperbolic cosine of a double value. |
|
Returns Euler's number e raised to the power of a double value. |
|
Returns ex -1. |
|
Returns the largest (closest to positive infinity) double value that is less than or equal to the argument and is equal to a mathematical integer. |
|
Returns the unbiased exponent used in the representation of a double. |
|
Returns the unbiased exponent used in the representation of a float. |
|
Returns sqrt(x2 +y2) without intermediate overflow or underflow. |
|
Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard. |
|
Returns the natural logarithm (base e) of a double value. |
|
Returns the base 10 logarithm of a double value. |
|
Returns the natural logarithm of the sum of the argument and 1. |
|
Returns the greater of two double values. |
|
Returns the greater of two float values. |
|
Returns the greater of two int values. |
|
Returns the greater of two long values. |
|
Returns the smaller of two double values. |
|
Returns the smaller of two float values. |
|
Returns the smaller of two int values. |
|
Returns the smaller of two long values. |
|
Returns the floating-point number adjacent to the first argument in the direction of the second argument. |
|
Returns the floating-point number adjacent to the first argument in the direction of the second argument. |
|
Returns the floating-point value adjacent to d in the direction of positive infinity. |
|
Returns the floating-point value adjacent to f in the direction of positive infinity. |
|
Returns the value of the first argument raised to the power of the second argument. |
|
Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0. |
|
Returns the double value that is closest in value to the argument and is equal to a mathematical integer. |
|
Returns the closest long to the argument. |
|
Returns the closest int to the argument. |
|
Return d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set. |
|
Return f × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the float value set |
|
Returns the signum function of the argument; zero if the argument is zero, 1.0 if the argument is greater than zero, -1.0 if the argument is less than zero. |
|
Returns the signum function of the argument; zero if the argument is zero, 1.0f if the argument is greater than zero, -1.0f if the argument is less than zero. |
|
Returns the trigonometric sine of an angle. |
|
Returns the hyperbolic sine of a double value. |
|
Returns the correctly rounded positive square root of a double value. |
|
Returns the trigonometric tangent of an angle. |
|
Returns the hyperbolic tangent of a double value. |
|
Converts an angle measured in radians to an approximately equivalent angle measured in degrees. |
|
Converts an angle measured in degrees to an approximately equivalent angle measured in radians. |
|
Returns the size of an ulp of the argument. |
|
Returns the size of an ulp of the argument. |
|
Returns the factorial of passed value. |
Regression functions
Function |
Description |
---|---|
|
Linear regression |
|
Quadratic regression |
|
General polynomial regression |
Special functions
Function |
Description |
||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Evaluates the passed expression with the specified filter. For example: filter('M_MEASURE > 100') { L_ID_COUNT } |
||||||||||||||||||||||||
|
Returns the aggregated value of the embedded expressio. The aggregation is computed for the specified upper level. For example: total = aggregatePrevLevel(1, { cumulateFromTime(actualYear(), { 'M_NAME_1' + 'M_NAME_2' }); }); current = cumulateFromTime(actualYear(), { 'M_NAME_1' + 'M_NAME_2' }); return current / total; |
||||||||||||||||||||||||
|
Computes the expression for desired drill-down member values. Example: return members('[L_LEVEL_1=VAL][L_LEVEL_2="VAL1,VAL2"]', { M_NAME_1 * M_NAME_2 }) This example returns the value of indicator |
||||||||||||||||||||||||
|
Computes the expression for particular members and calculates the sum from particular results. Following example shows the difference of using multiplication within the
|
||||||||||||||||||||||||
|
Computes the expression for particular members of the defined |
||||||||||||||||||||||||
|
Computes the expression for particular members and calculates the average value from particular results. |
||||||||||||||||||||||||
|
Computes the expression for particular members of the defined |
||||||||||||||||||||||||
|
Computes the expression for particular members and calculates the minimal value from particular results. |
||||||||||||||||||||||||
|
Computes the expression for particular members of the defined |
||||||||||||||||||||||||
|
Computes the expression for particular members and calculates the maximul value from particular results. |
||||||||||||||||||||||||
|
Computes the expression for particular members of the defined |
||||||||||||||||||||||||
|
Returns the value of current member. Example:
|
Passing parameters to time formula
It is possible to define the time entry by formula. Following functions are applicable for these kind of formulas only.
Function |
Description |
---|---|
|
Sets the parameter value. |
|
Loads the parameter value. |
|
Returns indicator from the context of the row of current table. This function returns also values of formula defined indicators (defined in report). Example: value(actualYear(),'actualMonth - 1m - 1d',indicator()) |
Consider following example - we have several indicators with codes M_NAME_1
, M_NAME_2
and M_NAME_3
. These indicators are used in formulas 1 - 3. The time area is defined by time formulas 1 - 5.
|
Time formula 1: |
Time formula 2: |
Time formula 3: |
Time formula 4: |
Time formula 5: |
Formula 1: |
1000 |
2000 |
3000 |
1000 |
1000 |
Formula 2: |
1000 |
2000 |
3000 |
2000 |
2000 |
Formula 3: |
1000 |
2000 |
3000 |
3000 |
3000 |
|
1000 |
2000 |
3000 |
N/A |
1000 |
|
1000 |
2000 |
3000 |
N/A |
2000 |
|
1000 |
2000 |
3000 |
N/A |
3000 |
Report variables
Report variables are accessible using the @
prefix. For example:
dateAt(@dateParameter, { return M_NAME_1; });
Variables can be used in formulas and in custom date/time interval definition.
Referencing data from another data set
|
Loads the indicator value from the specified data set. Data time context changing functions are available. |
|
Loads the indicator value from the specified data set. Data time context changing functions are available. |
Examples:
crossValue('DS_CODE', 'M_INDICATOR_CODE') crossValue('DS_CODE', '[L_LEVEL_A={member1,member2}][L_LEVEL_B={x}]', 'M_INDICATOR_CODE') dateAt('2012-01-01') { crossValue('DS_CODE', 'M_MEASURE') }