Formula Reference Guide NEW

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
`@SUM`	Calculates the sum of all values for the selected drill-down attribute	`M_NAME@SUM`
`@MIN`	Calculates the minimum of all values for the selected drill-down attribute	`M_NAME@MIN`
`@MAX`	Calculates the maximum of all values for the selected drill-down attribute	`M_NAME@MAX`
`@AVG`	Calculates the average of all values for the selected drill-down attribute	`M_NAME@AVG`
`@DC`	Calculates the distinct count of all values for the selected drill-down attribute	`M_NAME@DC`

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
`@SUMT`	Calculates the sum of all values for the selected time interval	`M_NAME@SUMT`
`@MINT`	Calculates the minimum of all values for the selected time interval	`M_NAME@MINT`
`@MAXT`	Calculates the maximum of all values for the selected time interval	`M_NAME@MAXT`
`@AVGT`	Calculates the average of all values for the selected time interval	`M_NAME@AVGT`
`@DCT`	Calculates the distinct count of all values for the selected time interval	`M_NAME@DCT`

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 in dd.MM.yyyy or yyyy-MM-dd format, or one of actualyear, actulamonth, actualquarter, actualweek, actualday, now
n represents the count of:
d days, w weeks, m months, q quarters or y 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
`dateAt(String dateString, { expression })`	Changes the context of the evaluated expression to `dateString` date. Example: dateAt('2011-01-01', { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; });
`dateInterval(String from, String to, { expression })`	Changes the date context of the expression and evaluates it aggregated in the specified interval `from` - `to`. Example: 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 `M_NAME_1` and `M_NAME_2` are aggregated for the whole period.
`timeAt(String timeString, { expression })`	Changes the context of the evaluated expression to `timeString` date. Example: timeAt('13:33:23', { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; });
`timeInterval(String from, String to, { expression })`	Changes the time context of the expression and evaluates it aggregated in the specified interval `from` - `to`. Example: 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 `M_NAME_1` and `M_NAME_2` are aggregated for the whole period.
`dateAt (String date, String period) { expression })`	Changes the context of the evaluated expression to `dateString` date - aggregated by the defined period {DAY, D, WEEK, W, MONTH, M, YEAR, Y}.
`dateInterval (String from, String to, String period ) { expression })`	Changes the date context of the expression and evaluates it aggregated in the specified interval `from` - `to`. Aggregation by the defined period {DAY, D, WEEK, W, MONTH, M, YEAR, Y} is also performed.
`timeAt (String time, String period) { expression })`	Changes the context of the evaluated expression to `timeString` time - aggregated by the defined period {HOUR, H, MINUTE, SECOND}.
`timeInterval (String from, String to, period) { expression })`	Changes the time context of the expression and evaluates it aggregated in the specified interval `from` - `to`. Aggregation by the defined period {HOUR, H, MINUTE, SECOND} is also performed.
`withoutDateTime() { expression })`	Evaluates the expression without date time interval.

Obsolete functions

These functions may be removed in further releases

Function	Description
`value(String dateString, String indicator)`	Loads the indicator's value at specified date.
`value(String dateString, int drill_down_level)`	Loads the indicator's value at specified date aggregated for N previous levels.
`value(String dateFrom, String dateTo, String indicator)`	Loads cumulative indicator's value for specified date interval.
`value(String dateFrom, String dateTo, String indicator, int dril_down_level)`	Loads cumulative indicator's value for specified date interval aggregated for N previous levels.

Advanced functions

Function

Description

cumulateFromDate(String startDate, String indicator)

This function gradually adds the current value to the cumulated value. Example:

	01/2011	02/2011	03/2011
`M_NAME_1`	1000	1200	1300
`cumulateFromDate('2011-01-01', 'M_NAME_1')`	1000	2200	3500

cumulateFromTime(String startTime, String indicator)

This function gradually adds the current value to the cumulated value. Example:

	00:01	00:02	00:03
`M_NAME_1`	1000	1200	1300
`cumulateFromTime('00:01', 'M_NAME_1')`	1000	2200	3500

prev(String indicatorCode)

Value of the passed indicator calculated for previous date or time value (e.g. previous month, day, hour, ...). Example:

	01/2011	02/2011	03/2011
`M_NAME_1`	1000	1200	1300
`prev('M_NAME_1')`		1000	1200

prev(String indicatorCode, int prevLevelAgg)

Value of the passed indicator calculated for previous date or time value (e.g. previous month, day, hour, ...). Parameter prevLevelAgg represents number of previous levels which should be aggregated.

next(String indicatorCode)

Value of the passed indicator calculated for next date or time value (e.g. previous month, day, hour, ...). Example:

	01/2011	02/2011	03/2011
`M_NAME_1`	1000	1200	1300
`next('M_NAME_1')`	1200	1300

next(String indicatorCode, int prevLevelAgg)

Value of the passed indicator calculated for next date or time value (e.g. previous month, day, hour, ...). Parameter prevLevelAgg represents number of previous levels which should be aggregated.

For changing the whole context of the evaluated expression, you can use following functions:

prev(String period, { expression })

Changes the context of the expression to desired previous date period. The value of the period parameter is one of: DAY, WEEK, MONTH, QUARTER, YEAR. Example:

	01/2011	02/2011	03/2011
`M_NAME_1`	1000	1200	1300
`prev(MONTH) { M_NAME_1 }`		1000	1200

next(String period, { expression })

Changes the context of the expression to desired next date period. The value of the period parameter is one of: DAY, WEEK, MONTH, QUARTER, YEAR

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
`String contextDay()`	Returns the formula's context day in `dd.MM.yyyy` format.
`String contextWeek()`	Returns the beginning of the context's week in `dd.MM.yyyy` format. Example:
`String contextMonth()`	Returns the beginning of the context's month in `dd.MM.yyyy` format.
`String contextQuarter()`	Returns the beginning of the context's year in `dd.MM.yyyy` format.
`String contextYear()`	Returns the formula's context week start in `dd.MM.yyyy` format.
`int dateDayOfYear()`	Returns the day of year from the context date
`int dateDayOfMonth()`	Returns the day of month from the context date
`int dateDayOfWeek()`	Returns the day of week from the context date
`int dateMonth()`	Returns the number of month from the context date
`int dateYear()`	Returns the number of month from the context date
`int timeHour()`	Returns the number of hour from the context time
`int timeMinute()`	Returns the number of minute from the context time
`int timeSecond()`	Returns the number of second from the context time
`int daysInMonth()`	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
`String actualDay()`	Returns the actual day in `dd.MM.yyyy` format
`String actualWeek()`	Returns the actual week in `dd.MM.yyyy` format
`String actualMonth()`	Returns the actual month in `dd.MM.yyyy` format
`String actualQuarter()`	Returns the actual quarter in `dd.MM.yyyy` format
`String actualYear()`	Returns the actual year in `dd.MM.yyyy` format

Math functions

Function	Description
`double abs(double a)`	Returns the absolute value of a double value.
`float abs(float a)`	Returns the absolute value of a float value.
`int abs(int a)`	Returns the absolute value of an int value.
`long abs(long a)`	Returns the absolute value of a long value.
`double acos(double a)`	Returns the arc cosine of a value; the returned angle is in the range 0.0 through pi.
`double asin(double a)`	Returns the arc sine of a value; the returned angle is in the range -pi/2 through pi/2.
`double atan(double a)`	Returns the arc tangent of a value; the returned angle is in the range -pi/2 through pi/2.
`double atan2(double y, double x)`	Returns the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta).
`double cbrt(double a)`	Returns the cube root of a double value.
`double ceil(double a)`	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.
`double copySign(double magnitude, double sign)`	Returns the first floating-point argument with the sign of the second floating-point argument.
`float copySign(float magnitude, float sign)`	Returns the first floating-point argument with the sign of the second floating-point argument.
`double cos(double a)`	Returns the trigonometric cosine of an angle.
`double cosh(double x)`	Returns the hyperbolic cosine of a double value.
`double exp(double a)`	Returns Euler's number e raised to the power of a double value.
`double expm1(double x)`	Returns ex -1.
`double floor(double a)`	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.
`int getExponent(double d)`	Returns the unbiased exponent used in the representation of a double.
`int getExponent(float f)`	Returns the unbiased exponent used in the representation of a float.
`double hypot(double x, double y)`	Returns sqrt(x2 +y2) without intermediate overflow or underflow.
`double IEEEremainder(double f1, double f2)`	Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard.
`double log(double a)`	Returns the natural logarithm (base e) of a double value.
`double log10(double a)`	Returns the base 10 logarithm of a double value.
`double log1p(double x)`	Returns the natural logarithm of the sum of the argument and 1.
`double max(double a, double b)`	Returns the greater of two double values.
`float max(float a, float b)`	Returns the greater of two float values.
`int max(int a, int b)`	Returns the greater of two int values.
`long max(long a, long b)`	Returns the greater of two long values.
`double min(double a, double b)`	Returns the smaller of two double values.
`float min(float a, float b)`	Returns the smaller of two float values.
`int min(int a, int b)`	Returns the smaller of two int values.
`long min(long a, long b)`	Returns the smaller of two long values.
`double nextAfter(double start, double dir)`	Returns the floating-point number adjacent to the first argument in the direction of the second argument.
`float nextAfter(float start, double dir)`	Returns the floating-point number adjacent to the first argument in the direction of the second argument.
`double nextUp(double d)`	Returns the floating-point value adjacent to d in the direction of positive infinity.
`float nextUp(float f)`	Returns the floating-point value adjacent to f in the direction of positive infinity.
`double pow(double a, double b)`	Returns the value of the first argument raised to the power of the second argument.
`double random()`	Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0.
`double rint(double a)`	Returns the double value that is closest in value to the argument and is equal to a mathematical integer.
`long round(double a)`	Returns the closest long to the argument.
`int round(float a)`	Returns the closest int to the argument.
`double scalb(double d, int scaleFactor)`	Return d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set.
`float scalb(float f, int scaleFactor)`	Return f × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the float value set
`double signum(double d)`	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.
`float signum(float f)`	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.
`double sin(double a)`	Returns the trigonometric sine of an angle.
`double sinh(double x)`	Returns the hyperbolic sine of a double value.
`double sqrt(double a)`	Returns the correctly rounded positive square root of a double value.
`double tan(double a)`	Returns the trigonometric tangent of an angle.
`double tanh(double x)`	Returns the hyperbolic tangent of a double value.
`double toDegrees(double angrad)`	Converts an angle measured in radians to an approximately equivalent angle measured in degrees.
`double toRadians(double angdeg)`	Converts an angle measured in degrees to an approximately equivalent angle measured in radians.
`double ulp(double d)`	Returns the size of an ulp of the argument.
`float ulp(float f)`	Returns the size of an ulp of the argument.
`long factorial(int value)`	Returns the factorial of passed value.

Regression functions

Function	Description
`linereg(String indicatorCode)`	Linear regression
`polyreg(2, String indicatorCode)`	Quadratic regression
`polyreg(3, String indicatorCode)`	General polynomial regression

Special functions

Function

Description

void filter(String filterExpression, { expression } )

Evaluates the passed expression with the specified filter. For example:

filter('M_MEASURE > 100') { L_ID_COUNT }

Double aggregatePrevLevel(int countOfPrevlevels, { expression } )

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;

Double members(String path, { expression } )

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 M_NAME_1 aggregated for specified members.

Double membersSum({ expression } )

Computes the expression for particular members and calculates the sum from particular results. Following example shows the difference of using multiplication within the membersSum() method and outside:

		Ind1	Ind2	membersSum( { M_IND_1 * M_IND_2 } )	M_IND_1 * M_IND_2
Member		5	30	80	150
Member	Child1	3	20	60	60
Member	Child2	2	10	20	20

Double membersSum(String levelCode, { expression } )

Computes the expression for particular members of the defined levelCode and calculates the sum from particular results.

Double membersAvg({ expression } )

Computes the expression for particular members and calculates the average value from particular results.

Double membersAvg(String levelCode, { expression } )

Computes the expression for particular members of the defined levelCode and calculates the average value from particular results.

Double membersMin({ expression } )

Computes the expression for particular members and calculates the minimal value from particular results.

Double membersMin(String levelCode, { expression } )

Computes the expression for particular members of the defined levelCode and calculates the minimal value from particular results.

Double membersMax({ expression } )

Computes the expression for particular members and calculates the maximul value from particular results.

Double membersMax(String levelCode, { expression } )

Computes the expression for particular members of the defined levelCode and calculates the maximum value from particular results.

String memberValue()

Returns the value of current member. Example:

	Population	memberValue()
Prague	1200000	Prague
Berlin	3000000	Berlin
London	7825200	London

rank() { expression }

Returns numerical order (rank) of the indicator applied in the expression within used drill-down. Example:

User	Score	rank()
Peter	90.3	2
John	92.7	1
Anna	89.7	3

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
`void set(String name, Object value)`	Sets the parameter value.
`Object get(String name)`	Loads the parameter value.
`Object indicator()`	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: `M_NAME_1`	Time formula 2: `M_NAME_2`	Time formula 3: `M_NAME_3`	Time formula 4: `s = get('suffix');` `return value('M_NAME' + s);`	Time formula 5: `value(actualYear(),` `'now-1m',indicator())`
Formula 1: `set('suffix', '_1');` `return M_NAME_1;`	1000	2000	3000	1000	1000
Formula 2: `set('suffix', '_2');` `return M_NAME_2;`	1000	2000	3000	2000	2000
Formula 3: `set('suffix', '_3');` `return M_NAME_3;`	1000	2000	3000	3000	3000
`M_NAME_1`	1000	2000	3000	N/A	1000
`M_NAME_2`	1000	2000	3000	N/A	2000
`M_NAME_3`	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

`crossValue(String dataSetCode, String indicator)`	Loads the indicator value from the specified data set. Data time context changing functions are available.
`crossValue(String dataSetCode, String membersIdentifier String indicator)`	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') 
}

Page tree

Indicators and codes

Drill-down (members) aggregation

Date-Time aggregation

Drill-down and date-time aggregation

Counting of level members

Datetime functions

Date strings

Changing datetime context

Advanced functions

Nested expressions

Working with datetime constants

Accessing context datetime

Accessing actual date

Math functions

Regression functions

Special functions

Passing parameters to time formula

Report variables

Referencing data from another data set

Next Steps