Syntax of Functions

Functions used in a text type target dimension:

Function

Description

Example

CLEAN (text; character)

Deletes the specified character in the text string.

CLEAN("moment" ;'m') = "oent"

COMPLETE (text; character; total length of code generated)

Completes a string using the character indicated as many times as necessary until the code reaches the indicated total length.

COMPLETE("RUB1" ;'0' ;8)=RUB10000

CONCAT (text1;text2;...)

Gathers together several character strings to create a single string.

Text1;text2;... represents the text elements to be grouped together in a single string.

CONCAT("Total ";"Value") = "Total Value"

FIXED (number; decimals; no_separator)
Rounds a number up or down to the specified number of decimal places and returns the result in text form.

  • Number: represents the number to be rounded up or down and converted into text.

  • Decimals: represents the number of decimals after the point.

  • No_separator: represents a logical value which, when equal to 1 (default value), allows you to avoid spaces being inserted in the text returned by FIXED.

If no value is indicated for "no_separator", the text returned will include spaces.

FIXED(1234.567; 1)="1 234.6"

LEFT (text; nb_characters)

Returns the indicated number of characters starting from the left of the text string.

Nb_characters must be greater than or equal to zero.

If nb_characters is greater than the text length, LEFT returns the whole text.

LEFT("Sales Price"; 4)="Sale"

MID (text; no_start; no_car)

Returns a given number of characters extracted from a text string starting from the point specified.

Text: represents the text string containing the characters to be extracted.

  • no_start: indicates the position of the first character to be extracted.

    If no_start is less or greater than the length of the text, MID returns all characters until the end of the text.

  • no_car: indicates the number of characters to be extracted from the text.

MID("chapter" ; 3 ; 2)="ap"

REPLACE (old_text; no_start; no_char; new_text)
Replaces one text string with another.

  • old_text: represents the text containing the characters you want to replace.

  • no_start: indicates the position of the first character in the old_text string to be replaced by the new_text string.

  • no_car: indicates the number of characters in the old_text string that the new_text string must replace.

  • new_text: represents the text that must replace the old_text.

REPLACE("1990"; 3; 2; "91")="1991"

REPT (text; no_times)
Repeats a text a certain number of times.

  • no_times: represents a positive number indicating the number of times the text must be repeated.

    • If no_times is equal to zero, the REPT function sends back empty text ("").

    • If no_times is not a whole number, it will be truncated.

REPT("*-"; 3) = "*-*-*-"

RIGHT (text; nb_characters)

Returns the indicated number of characters starting from the far right-side of the text string.

Nb_characters must be greater than or equal to zero.

If nb_characters is greater than the length of the text string, the RIGHT function will return the whole text.

RIGHT("Sales price"; 5)="price"

SUBSTITUTE (text; old_text; new_text; no_position)

Replaces the argument in old_text with new_text in a text string.

  • text: represents the text containing characters you want to replace.

  • old_text: represents the text to be replaced.

  • new_text: represents the text to replace old_text.

  • no_position: specifies the block of old_text you want to replace with new_text.

    • If you specify no_position, only the corresponding block of old_text will be replaced.

    • If you do not specify no_position, all blocks of the old_text will be replaced with new_text.

SUBSTITUTE("Sales"; "Sales"; "Cost") = "Cost"

TRIM (text)

Deletes all spaces between text except spaces between words.

TRIM(Turnover...first....quarter ") = " Turnover first quarter "

Functions used in a numerical target dimension:

Function

Description

Example

+

Add

123+123=246

-

Subtract

123-122=1

*

Multiply

12*11=132

/

Divide

144/12=12

ABS (number)

Returns the absolute value of a number.

ABS(2)=2 ABS(-2)=2

LEN (text)

Gives the length of a string of characters.

Spaces are counted as characters.

LEN("string")=6

VALUE (text)

Converts a string of characters representing a number into a number. The number must be represented without a thousand separator and with . as the decimal separator.

VALUE("3352.14") = 3352.14