Function dictionary

abs

Syntax	abs(number) : number
Arguments	value: The value for which find the absolute value.
Category	Numeric
Context	All Returns the absolute value of the given number.

Example Expression	Expected Result
abs(–12.34)	12.34

add

Syntax	add(number,number) : number
Arguments	val1: The first value to add val2: The second value to add
Category	Numeric
Context	All Adds 2 number values. Note that a number added to null returns the original value.

Example Expression	Expected Result
1 + 2	3
1 + NULL	1

addAttribute

Syntax	`addAttribute(attributes,text,a) : attributes`
Arguments	attributes: The set of content attributes attrName: The name of the attribute attrValue: The attribute value
Category	Attributes
Context	All Adds an additional attribute with no condition to a set of content attributes with the specified name and value. This function is similar to the attribute() function, except that it adds the attribute to the set specified in the first argument instead of returning a new set.

Example Expression	Expected Result
addAttribute(<FIXED 1>, “FONT_FACE”, “Arial”)	<FIXED 1; FONT_FACE “Arial”>

addDays

Syntax	`addDays(time,number) : time`
Arguments	timeVal: The time value to be shifted nDayofmonth: The number of days to shift the time value
Category	Time
Context	All Adds a number of days to a time value. A negative shift value can be used to shift the time to a previous day. The shifting is sensitive to the user’s time zone (where the shift crosses a DST boundary).

Example Expression	Expected Result
addDays(toDate(2010,7,15), 2)	2010/07/17
addDays(toDate(2012,03,01), –1)	2012/02/29

addErrorConditionAttribute

Syntax	`addErrorConditionAttribute(attributes,text,a) : attributes`
Arguments	attributes: The set of content attributes attrName: The name of the attribute attrValue: The attribute value
Category	Attributes
Context	All Adds an attribute with an “Is Error” value condition to a set of content attributes. The attribute will apply when the current data has an error value.

Example Expression	Expected Result
addErrorConditionAttribute(<FIXED 1>, “FONT_FACE”, “Arial”)	<FIXED 1; Error<FONT_FACE “Arial”>>

addMonths

Syntax	`addMonths(time,number) : time`
Arguments	timeVal: The time value to be shifted. nMonth: The number of months to shift the time value.
Category	Time
Context	All Adds a number of months to a time value. A negative shift value can be used to shift the time to a previous month. The shifting is sensitive to the user’s time zone (where the shift crosses a DST boundary).

Example Expression	Expected Result
addMonths(toDate(2010,7,15), 2)	2010/09/15
addMonths(toDate(2012,03,30), –1)	2012/02/29

addNullConditionAttribute

Syntax	`addNullConditionAttribute(attributes,text,a) : attributes`
Arguments	attributes: The set of content attributes attrName: The name of the attribute attrValue: The attribute value
Category	Attributes
Context	All Adds an attribute with an “Is Null” value condition to a set of content attributes. The attribute will apply when the current data has a Null value.

Example Expression	Expected Result
addNullConditionAttribute(<FIXED 1>, “FONT_FACE”, “Arial”)	<FIXED 1; Null<FONT_FACE “Arial”>>

addRangeConditionAttribute

Syntax	`addRangeConditionAttribute(attributes,text,a,number,number) : attributes`
Arguments	attributes: The set of content attributes attrName: The name of the attribute attrValue: The attribute value rangestart: The lower bound of the range condition, or Null for no lower bound rangeEnd: The upper bound of the range condition, or Null for no upper bound
Category	Attributes
Context	All Adds an attribute with a range condition to a set of content attributes. The attribute will apply when the current data value falls within the value range specified. Null values can be specified for the start or end of the range to indicate no lower or upper bound. If specified, the range start and end values are inclusive.

Example Expression	Expected Result
addRangeConditionAttribute(<FIXED 1>, “FONT_FACE”, “Arial”, 3, 5)	<FIXED 1; Range 3:5<FONT_FACE “Arial”>>
addRangeConditionAttribute(<FIXED 1>, “FONT_FACE”, “Arial”, 3, Null)	<FIXED 1; Range 3:<FONT_FACE “Arial”>>

addSeconds

Syntax	`addSeconds(time,number) : time`
Arguments	timeVal: The time value to be shifted nSecond: The number of seconds to shift the time value
Category	Time
Context	All Adds a number of seconds to a time value. A negative shift value can be used to shift the time to a previous second. The shifting is sensitive to the user’s time zone (where the shift crosses a DST boundary).

Example Expression	Expected Result
addSeconds(toDate(2010,7,15), 2)	2010/07/15 00:00:02
addSeconds(toDate(2012,3,1), –3600)	2012/02/29 23:00:00

addYears

Syntax	`addYears(time,number) : time`
Arguments	timeVal: The time value to be shifted. nYear: The number of years to shift the time value.
Category	Time
Context	All Adds a number of years to a time value. A negative shift value can be used to shift the time to a previous year. The shifting is sensitive to the user’s time zone (where the shift crosses a DST boundary).

Example Expression	Expected Result
addYears(toDate(2010,7,15), 2	2012/07/15
addYears(toDate(2012,02,29), –1)	2011/02/28

adjustTimeFilterPeriod

Syntax	adjustTimeFilterPeriod(a, time, text, number, number) : a
Arguments	val: The value expression to be computed. filterTime: The time value for which the filter will be added/updated. timePeriod: The name of the time period to be used for constructing the new filter time range. nPeriodsStartOffset: The time period offset start. nPeriods: The number of periods. This must be greater or equal to 1.
Category	Reference, Time
Context	field, query, report Computes values for time ranges that are relative to a time filter in the current query.

Example Expression	Expected Result
adjustTimeFilterPeriod( [Sum OF [Sales]], [Order Date], 'Month', -1, 1)	1000

and

Syntax	and(bool,bool) : bool
Arguments	bool1: The first Boolean argument bool2: The second Boolean argument
Category	Logical
Context	All Returns True if both of the argument values are True. Otherwise it returns False.

Example Expression	Expected Result
and(true, false)	false (0)
and(true, true)	true (1)

append

Syntax	append(text,text) : text append(attributes,attributes) : attributes
Arguments	val1: The first value to append val2: The second value to append
Category	Append
Context	All Concatenates 2 values (text, attributes, lists, and so on). Note that Null values will be ignored when appended to other values.

Example Expression	Expected Result
'testing' & '123'	'testing123'
NULL & 'abc'	'abc'
{1, 2, 3} & {3, 4, 5}	{1, 2, 3, 3, 4, 5}
<BOLD; FORECOLOR red> & <UNDERLINE>	<BOLD; FORECOLOR red; UNDERLINE>

aqua

Syntax

aqua() : color

Category

Color

Context

All

The color aqua.

attribute

Syntax	`attribute(text,a) : attributes`
Arguments	attrName: The name of the attribute attrValue: The attribute value
Category	Attributes
Context	All Returns a single, unconditional attribute with the specified name and value.

Example Expression	Expected Result
attribute(“FONT_FACE”, “Arial”)	<FONT_FACE “Arial”>
attribute(“FIXED”, 1)	<FIXED 1>
attribute(“FORECOLOR”, blue)	<FORECOLOR blue>

avg

Syntax	`avg(number) : number`
Arguments	val: The values to aggregate
Category	Aggregation
Context	field, query, report Returns the arithmetic mean of all of the data values for a given field.

Example Expression	Expected Result
avg([Salary])	65000.0

avgOfList

Syntax	`avgOfList(list<number>) : number`
Arguments	list: The list of values whose average to find
Category	List
Context	All Returns the average of the non-null values in a given list. The list can be passed to the function as a literal list enclosed in braces or as a sequence of arguments.

Example Expression	Expected Result
avgOfList({1,null,2})	1.50
avgOfList(1,null,2)	1.50

black

Syntax

black() : color

Category

Color

Context

All

The color black.

blue

Syntax

blue() : color

Category

Color

Context

All

The color blue.

capture

Syntax	`capture(text,text,number) : text`
Arguments	regex: The regular expression pattern (with required capture pattern). text: The body text to scan for the pattern match. index: The capture pattern index indicating which text to return.
Category	Text
Context	All Returns the text that matches a given regular expression.

Example Expression	Expected Result
capture("(\d+)-(\d+)-(\d+)", "123-456-789", 2)	456
capture(".(foo).", "myfoo2", 0)	myfoo2
capture(".(foo).", "myfoo2", 1)	foo

ceiling

Syntax	`ceiling(number) : number`
Arguments	value: The numeric value to be rounded.
Category	Numeric
Context	All Returns the smallest integer value that is greater than or equal to the argument.

Example Expression	Expected Result
ceiling(1.9)	2
ceiling(–1.9)	–1

char

Syntax	`char(number) : text`
Arguments	code: Unicode character value
Category	Text
Context	All The character representing the given unicode value.

Example Expression	Expected Result
char(65)	A

charCode

Syntax	`charCode(text) : number`
Arguments	text: The text.
Category	Text
Context	All Returns the unicode character value of the first character in the given text.

Example Expression	Expected Result
charCode(“ABC”)	65

clean

Syntax	`clean(text) : text`
Arguments	text: The original text.
Category	Text
Context	All Removes all of the non-printable characters from a specified text.

Example Expression	Expected Result
clean([Description Text])	Nice text

coalesce

Syntax	`coalesce(list<a>) : a`
Arguments	vals: A list of values.
Category	Value
Context	All Returns the value of the first non-null, non-error argument.

Example Expression	Expected Result
coalesce(null, null, 1, null, 2)	1
coalesce(null, null)	null

color

Syntax	`color(text) : color` color(number,number,number) : color color(number,number,number,number) : color
Argument	color: A color string. R: The red component value (0–255). G: The green component value (0–255). B: The blue component value (0–255). alpha: The alpha component value (0–255).
Category	Color
Context	All Construct a color value. The arguments can be red, green, blue, and optionally alpha component values (0–255). Alternatively, a color string can be specified containing: Comma-separated RGB values (with or without an alpha value) Hex color string X11 color name

Example Expression	Expected Result
color(0 , 255, 0)
color(255, 0, 0, 128)
color(“255,0,255”)
color(“#00FF00”)
color(“Dark Orange”)

contains

Syntax	`contains(text,text) : bool`
Arguments	regex: The regular expression pattern. text: The body text to scan for the pattern match.
Category	Text
Context	All Determines if a pattern of text occurs within the given source text.

Example Expression	Expected Result
contains("o*b”, “foobar”)	true (1)
contains("o”, “foobar”)	true (1)
contains("oa”, “foobar”)	false (0)

containsAll

Syntax	`containsAll(list<a>,list<a>) : bool`
Arguments	list1: The first list. list2: The second list.
Syntax	containsAll(set<a>,set<a>) : bool
Arguments	set1: The first set. set2: The second set.
Category	List
Context	All Checks if the first list contains all values in the second list.

Example Expression	Expected Result
containsAll({1, 2, 3, 4} {2, 4})	True
containsAll({1, 2, 3, 4} {3, 5})	False

containsAny

Syntax	`containsAny(list<a>,list<a>) : bool`
Arguments	list1: The first list list2: The second list
Syntax	containsAny(set<a>,set<a>) : bool
Arguments	set1: The first set set2: The second set
Category	List
Context	All Checks if the first list contains any of the values in the second list.

Example Expression	Expected Result
containsAny({1, 2, 3, 4} {2, 5})	True
containsAll({1, 2, 3, 4} {5, 7})	False

containsAttribute

Syntax	`containsAttribute(attributes,text) : bool`
Arguments	attributes: The set of content attributes (conditional and/or unconditional). attrName: The name of the attribute to check for.
Category	Attributes
Context	All Checks whether the specified attribute appears in the set of content attributes (with or without a condition).

Example Expression	Expected Result
containsAttribute(<FIXED 1; Error<FONT_FACE "Arial";>>, "FIXED")	True
containsAttribute(<FIXED 1; Error<FONT_FACE "Arial";>>, "FONT_FACE")	True
containsAttribute(<FIXED 1; Error<FONT_FACE "Arial";>>, "ABC")	False

continuousValueFeature

Syntax	continuousValueFeature(number,number) : number continuousValueFeature(time,number) : number
Arguments	featureProp: The continuous (number or time) value property on which the feature is based weighting: A weighting factor that will give more influence to features with higher weighting values (default =- 1)
Category	Reference
Context	field, query, report Specifies the configuration for a continuous (number or time) value feature to be used when querying similar values.

Example Expression	Expected Result
continuousValueFeature([Contact].[Duration], 0.5)	null
continuousValueFeature([Country].[Population], 1.0)	null
continuousValueFeature([Contact].[StartTime], 0.5)	null

contributionDate

Syntax	`contributionDate() : time`
Arguments	—
Category	System
Context	Column Returns the date/time associated with the data contribution containing the current row. The default is the date on which the data containing the row was uploaded. The official contribution date can also be provided by the user or via the program interface.

Example Expression	Expected Result
contributionDate())	2011

contributionId

Syntax	`contributionId() : number`
Arguments	—
Category	System
Context	Column Returns a unique ID for the data contribution containing the current row.

Example Expression	Expected Result
contributionId()	123456

contributionRowId

Syntax	`contributionRowId() : number`
Arguments	—
Category	System
Context	Column Returns the row number for the current row within its data contribution.

Example Expression	Expected Result
contributionRowId()	1

contributionSecondaryRowId

Syntax	`contributionSecondaryRowId() : number`
Arguments	—
Category	System
Context	Column Returns the row number for the current row within its secondary division in the data contribution. Used for differentiating row count for nested XPaths.

Example Expression	Expected Result
contributionSecondaryRowId()	1

count

Syntax	`count(a) : number`
Arguments	val: The values to aggregate
Category	Aggregation
Contexts	Field, query, report Returns the total number of non-null and non-error data values for a given field, even if some records are not unique. To count the number of unique records instead, use the distinctCount function.

Example Expression	Expected Result
count({Employee})	43

countOfList

Syntax	`countOfList(list<a>) : number`
Arguments	list: The list of values to find the count.
Category	List
Context	All Returns the count of the non-null values in a given list. The list can be passed to the function as a literal list enclosed in braces or as a sequence of arguments.

Example Expression	Expected Result
countOfList({1,null,2})	2
countOfList(1,null,2)	2
countOfList(‘c’, ‘a’, ‘t’)	3

currentMeasureValue

Syntax	currentMeasureValue() : number
Arguments
Category	Reference
Context	All A placeholder for the value of the current measure.

Example Expression	Expected Result
currentMeasureValue() + 1	7

datasetName

Syntax	`datasetName() : text`
Arguments	—
Category	System
Contexts	Column Returns the name of the data set containing the current row.

Example Expression	Expected Result
datasetName()	UK Division

dateToUnixTimeSeconds

Syntax	`dateToUnixTimeSeconds(time) : number`
Arguments	date: The date to convert to a Unix time value in seconds
Categories	Conversion, Time
Contexts	All Converts a date-time value to the Unix timestamp value (number of seconds since Jan 1, 1970 UTC)

Example Expression	Expected Result
dateToUnixTimeSeconds(toDate(2015, 1, 1)))	1420070400

dateTrim

Syntax	`dateTrim(time,text) : time`
Arguments	date: The time to trim. timePeriod: The name of the time period to trim to.
Category	Time
Context	All Returns a data value that is trimmed to the specified precision. The date can be trimmed to the minute, hour, day, month, or year. The second argument controls how the date value is trimmed. Works with the name of the time period: year, month, quarter, week.

Example Expression	Expected Result
month(dateTrim({GoodsIn}, “dayOfMonth”))	3
month(dateTrim({GoodsIn}, “year”))	1

dateTrimSecondsUTC

Syntax	`dateTrimSecondsUTC(time,number) : time`
Arguments	time: The time to trim seconds: The number of seconds used to trim to
Category	Time
Context	All Truncates time values to a given interval in seconds

Example Expression	Expected Result
`dateTrimSecondsUTC(toDate(2015, 3, 10, 20, 4), 1800)`	2015-03-10 20:00:00 UTC
dateTrimSecondsUTC(toDate(2015, 3, 10, 20, 38), 1800)	2015-03-10 20:30:00 UTC
dateTrimSecondsUTC(toDate(2015, 3, 10, 20, 38), 1200)	2015-03-10 20:20:00 UTC

day

Syntax	`day(time) : number`
Arguments	time: The time from which to extract the day
Category	Time
Contexts	All Returns the day of the given date-time value

Example Expression	Expected Result
day([BonusDate])	1

deleteAll

Syntax	`deleteAll(list<a>,list<a>) : list<a>`
Arguments	list: The list to delete elements from elements to delete: The elements to delete from the list
Categories	List
Contexts	All Deletes all specified elements from a list.

Example Expression	Expected Result
deleteAll({1,2,3},{2})	{1,3}
deleteAll({1,2,3,4,5},{1,5})	{2,3,4}
deleteAll({1,1,1,1,1},{1})	{}
deleteAll({},{1,2,3})	{}

deleteAt

Syntax	`deleteAt(list<a>,number) : list<a>`
Arguments	list: The list to delete elements from index: The index of the element to delete
Categories	List
Contexts	All Returns the list without the specified element.

Example Expression	Expected Result
deleteAt({1,2},{1})	{2}
deleteAt({1,2},2)	{1}
`deleteAt({},50)`	{}
deleteAt({1, 2, 3},–1)	{1, 2, 3}

diffDays

Syntax	`diffDays(time,time) : number`
Arguments	time1: The first date-time value time1: The second date-time value
Category	Time
Context	All Returns the number of days between two dates. If the first date is after the second date then the return value will be positive, otherwise it will be negative.

Example Expression	Expected Result
diffDays(toDate("yyyy", "2001"), toDate("yyyy", "2000"))	366
diffDays(toDate("yyyy", "2002"), toDate("yyyy", "2001"))	365

diffSeconds

Syntax	`diffSeconds(time,time) : number`
Arguments	time1: The first date-time value time2: The second date-time value
Category	Time
Contexts	All Returns the number of seconds between two given time values. If the first time is after the second time, then the returned value is positive, otherwise it is negative.

Example Expression	Expected Result
diffSeconds(toDate("yyyy-hhmm", "2001-1030"), toDate("yyyy-hhmm", "2001-1029"))	60

discreteValueFeature

Syntax	discreteValueFeature(a,number) : number
Arguments	featureProp: The discrete value property on which the feature is based weighting: A weighting factor which will give more influence to features with higher weighting values (default = 1)
Category	Reference
Context	field, query, report Specifies the configuration for a discrete value feature to be used when querying similar values.

Example Expression	Expected Result
discreteValueFeature([Contact].[Agent], 1.0)	null
discreteValueFeature([Country].[Language], 0.5)	null

distinctCount

Syntax	`distinctCount(a) : number`
Arguments	val: The values to aggregate.
Category	Aggregation
Contexts	field, query, report Returns the number of unique non-null and non-error data values for a given field. To count the total number of records instead, use the count function.

Example Expression	Expected Result
distinctCount([Employee])	14.0

distinctCountOfList

Syntax	`distinctCountOfList(list<a>) : number`
Arguments	list: The list of values to find the distinct count
Category	List
Context	All Returns the distinct count of the non-null values in a given list. The list can be passed to the function as a literal list enclosed in braces or as a sequence of arguments.

Example Expression	Expected Result
distinctCountOfList({1,null,1,2})	2
distinctCountOfList(1,null,1,2)	2

divide

Syntax	divide(number,number) : number divide(number,number,number) : number
Arguments	num: The numerator value denom: The denominator value fallback: The fallback value
Category	Numeric
Context	All Divides one number by another. If either value is null, then the result is null. An error value is returned if the divisor is zero. Instead of returning an error, an optional third argument representing a fallback value can be specified. If the divisor is zero, the fallback value is returned.

Example Expression	Expected Result
6 / 2	3
6 / NULL	NULL
6 / (1 - 1)	ERR{DivideByZero}
divide(5, 1, NULL)	5
divide(5, 1, error('fallback error'))	5
divide(error('a'), error('b'), 3)	ERR{a, b}
divide(NULL, 0, 3)	NULL
divide(0, NULL, 3)	NULL

drop

Syntax	drop(number,list<a>) : list<a>
Arguments	number: The number of values to drop from the provided list list: The list to discard values from
Category	List
Context	All Returns the given list with a number of values discarded from the start of the list.

Example Expression	Expected Result
drop(2, {"do", "not", "never", "do"})	{"never", "do"}
drop(3, {1,2})	{}
drop(3, {})	{}

dropLast

Syntax	dropLast(list<a>) : list<a>
Arguments	list: The list to discard the last value from.
Category	List
Context	All Returns the given list with the last value discarded.

Example Expression	Expected Result
dropLast({"do", "not", "never", "do"})	{"do", "not", "never"}
dropLast({1,2})	{1}
dropLast({})	{}

dropPrefix

Syntax	dropPrefix(text,text) : text
Arguments	prefix: The prefix to remove text: The text to remove the prefix from
Category	Text
Context	All Drops a specified prefix from a text value if it exists. If the prefix does not exist, the text is returned unmodified.

Example Expression	Expected Result
dropPrefix("The ", "The Fox")	"Fox"
dropPrefix("The ", "A Fox")	"A Fox"

dropSuffix

Syntax	dropSuffix(text,text) : text
Arguments	suffix: The suffix to remove text: The text to remove the suffix from
Category	Text
Context	All Drops a specified suffix from a text value if it exists. If the suffix does not exist, the text is returned unmodified.

Example Expression	Expected Result
dropSuffix(" Fox", "The Fox")	"The"
dropSuffix(" Dog", "The Fox")	"The Fox"

each

Syntax	`each(list<a>) : a`
Arguments	list: The list of values to be expanded.
Category	List
Contexts	field, query Expands a list value into multiple rows of data.

endsWith

Syntax	`endsWith(list<a>,list<a>) : bool`
Arguments	possibleSuffix: The list to be checked for at the end of the list. list: The list that might contain the suffix list.
Category	List
Context	All Returns True if the first list is a suffix of the second list.

Example Expression	Expected Result
endsWith({3}, {1, 2, 3})	True
endsWith({5, 6, 7}, {1, 2, 3})	False

endsWith

Syntax	`endsWith(list<a>,list<a>) : bool`
Arguments	possibleSuffix: The list to be checked for at the end of the list. list: The list that might contain the suffix list.
Category	List
Context	All Returns True if the first list is a suffix of the second list.

Example Expression	Expected Result
endsWith({3}, {1, 2, 3})	True
endsWith({5, 6, 7}, {1, 2, 3})	False

equals

Syntax	equals(a,a) : bool
Arguments	val1: The first value to compare val2: The second value to compare
Category	Comparison
Context	All Returns True if the first value is equal to the second value. Note that comparing against Null is allowed.

Example Expression	Expected Result
5 = 10	false
5 = 5	true
Null = 5	false
Null = Null	true

errorConditionAttribute

Syntax	`errorConditionAttribute(text,a) : attributes`
Arguments	attrName: The name of the attribute attrValue: The attribute value
Category	Attributes
Contexts	All Construct a content attribute containing a single attribute with an “Is Error” condition.

Example Expression	Expected Result
errorConditionAttribute("FIXED", 1)	<Error<FIXED 1>>

factorial

Syntax	factorial(number) : number
Arguments	value: The numeric value to evaluate the factorial function at
Category	Numeric
Context	All Returns the factorial of a given value. Due to imprecision of floating point numbers, this loses some precision after factorial (18).

Example Expression	Expected Result
factorial(3)	6
factorial(0)	1
factorial(-1)	1
factorial(3.5)	6

filteredValue

Syntax	filteredValue(a,bool) : a
Arguments	val: The expression to evaluate with the modified filter filter: A filter to be applied to the specified value
Category	field, query, report Returns the value of the first argument with the specified filter applied.
Context

Example Expression	Expected Result
filteredValue([Sales], [Product].[Size] > 7)	4000
filteredValue([SUM OF [Sales]] - [SUM OF [Costs]], [Country] = 'Canada')	2000

find

Syntax	`find(a,list<a>) : number`
Arguments	x: The item to find in the list List: The list that might contain the item
Syntax	`find(text,text) : number`
Arguments	searchText: The text to search for text: The body text in which to locate searchText
Syntax	`find(text,text,number) : number`
Arguments	searchText: The text to search for text: The body text in which to locate searchText start: The character location in text at which to start searching
Category	List
Context	All Find the index of the given item in the given list starting at the beginning of the list or 0 if the item is not found in the list

Example Expression	Expected Result
find("a",{"a","b","a","b"})	1
find("z",{"a","b","a","b"})	0

findLast

Syntax	`findLast(a,list<a>) : number`
Arguments	x: The item to find in the list List: The list that might contain the item
Syntax	`findLast(text,text) : number`
Arguments	searchText: The text to search for text: The body text in which to locate searchText
Syntax	`findLast(text,text,number) : number`
Arguments	searchText: The text to search for text: The body text in which to locate searchText start: The character location in text at which to start searching
Category	List
Context	All Find the index of the given item in the given list starting at the end of the list.

Example Expression	Expected Result
findLast("a",{"a","b","a","b"})	3

floor

Syntax	`floor(number) : number`
Arguments	value: he numeric value to be rounded
Category	Numeric
Context	All Returns the smallest integer value that is less than or equal to the argument.

Example Expression	Expected Result
floor(1.9)	1
floor(–1.9)	–2

format

Syntax	`format(attributes,a) : text`
Arguments	attributes: The attributes to format the value with value: The value to be formatted
Category	Format
Context	All Returns the formatted text for the given value and formatting attributes set.

Example Expression	Expected Result
format(<FIXED> 3>, 1)	1.000

fuchsia

Syntax

fuchsia() : color

Category

Color

Context

All

The color fuchsia.

getCurrentUser

Syntax	`getCurrentUser() : text`
Arguments	—
Category	System
Context	field, query, report Returns the account name of the current user.

Example Expression	Expected Result
getCurrentUser()	[email protected]

grandTotalValue

Syntax	`grandTotalValue(a) : a`
Arguments	val: The expression to evaluate in the modified (grand total) context
Category	Reference
Context	field, query Returns the grand total value for the specified expression across all context dimensions. This can be used to generate comparisons to specific values.

Example Expression	Expected Result
[Sales] / grandTotalValue([Sales])	0.23

gray

Syntax

gray() : color

Category

Color

Context

All

The color gray.

greaterThan

Syntax	greaterThan(a,a) : bool
Arguments	val1: The first value to compare val2: The second value to compare
Category	Comparison
Context	All Returns True if the first value is greater than the second value.

Example Expression	Expected Result
10 > 5	true
5 > 10	false
5 > 5	false

greaterThanEquals

Syntax	greaterThanEquals(a,a) : bool
Arguments	val1: The first value to compare val2: The second value to compare
Category	Comparison
Context	All Returns True if the first value is greater than or equal to the second value.

Example Expression	Expected Result
5 >= 10	false
5>= 5	true

green

Syntax

green() : color

Category

Color

Context

All

The color green.

group

Syntax	group(list<a>) : list<list<a>>
Arguments	list: The list to group together values with
Category	List
Context	All Returns the provided list with subsequent equal values grouped together into a sublist.

Example Expression	Expected Result
group({"hello","hello","goodbye","hello"})	{{"hello", "hello"}, {"goodbye"}, {"hello"}}
group({"a", "a", "b", "b"})	{{"a", "a"}, {"b", "b"}}
group({1,2,2,3,3,3,4,4,4,4})	{{1}, {2, 2}, {3, 3, 3}, {4, 4, 4, 4}}
group({"a"})	{{"a"}}
group({})	{}

head

Syntax	`head(list<a>) : a`
Arguments	list: The list from which to take the first element.
Category	List
Context	All Returns the first element of the list. If the list is empty, returns null.

Example Expression	Expected Result
head({1,2,3,4})	1
head({1})	1
head({})	null

hour

Syntax	`hour(time) : number`
Arguments	time: The time from which to extract the hour
Category	Time
Context	All Returns the hour of the given date-time value.

Example Expression	Expected Result
hour([BonusDate])	23

if

Syntax	`if(bool,a,a) : a`
Arguments	condition: The Boolean condition to be tested thenVal: The value to be returned if the condition is true elseVal: The value to be returned if the condition is false
Category	Control
Context	All Returns the second argument (the THEN value) if the condition value (the first argument) is true or the third argument (the ELSE value) if the condition value is false. If the condition value is Null, then null is returned.

Example Expression	Expected Result
IF True THEN 123 ELSE NULL	123
IF NULL THEN 123 ELSE 999	NULL
IF(1 > 2, 'abc', 'def')	'def'

ifEmpty

Syntax	`ifEmpty(a,a) : a`
Arguments	value: The value to check valueIfEmpty: The value to use if the first argument is empty.
Category	Value
Context	All Evaluates the first argument to determine if it is empty. If it is not empty, the first argument is returned. Otherwise, the second argument is returned.

Example Expression	Expected Result
ifEmpty("", "nonEmptyText")	nonEmptyTest
ifEmpty({}, {1,2,3})	{1,2,3}

ifError

Syntax	`ifError(a,a) : a`
Arguments	value: The value to check valueIfEmpty: The value to use if the first argument is empty.
Category	Error, Value
Context	All Evaluates the first argument to determine if it is an error. If there is no error, the first argument is returned. Otherwise, the second argument is returned.

Example Expression	Expected Result
ifError(5, 10)	5
ifError(5 / 0, 10)	10

ifNotValue

Syntax	`ifNotValue(a,a) : a`
Arguments	value: The value to check valueIfNullError: The value to use if the first argument is an error or null.
Category	Value
Context	All Evaluates the first argument to determine if it has a value (it is not null or not an error). If it has a value, the first argument is returned. Otherwise, the second argument is returned.

Example Expression	Expected Result
ifNotValue(5, 10)	5
ifNotValue(5 / 0, 10)	10
ifNotValue(NULL, 10)	10

ifNull

Syntax	`ifNull(a,a) : a`
Arguments	value: The value to check valueIfNull: The value to use if the first argument is null
Category	Value
Context	All Evaluates the first argument to determine if it is null. If it is not null, the first argument is returned. Otherwise, the second argument is returned.

Example Expression	Expected Result
ifNull(5, 10)	5
ifNull(NULL, 10)	10

inRange

Syntax	inRange(a,range<a>) : bool inRange(a,a,a) : bool inRange(a,a,bool,a,bool) : bool
Arguments	value: The value to check range: The range of values rangeStart: The inclusive start of the value range rangeEnd: The inclusive end of the value range startInclusive: True if the start of the range (if any) is inclusive endInclusive: True if the end of the range (if any) is inclusive
Category	Range
Context	All Returns whether a value is included in the specified range.

Example Expression	Expected Result
inRange(5, range(1, 10))	True
inRange(12, range(1, 10))	False
inRange(12, range(10, null))	True
inRange(10, range(1, False, 10, False))	False

insertAt

Syntax	`insertAt(list<a>,number,a) : list<a>`
Arguments	list: The list to insert the value into index: The index at which to insert the value value: The value to insert
Category	List
Context	All Inserts a value at a given index.

Example Expression	Expected Result
insertAt({1,2,3},2,4)	{1,4,2,3}
insertAt({4,6,2,3},1,9)	{9,4,6,2,3}
insertAt({9,4,2},100,29)	{9,4,2,29}
insertAt({1,2,3},–200,111)	{111,2,3}
insertAt({},0,1)	{1}

intersection

Syntax	`intersection(list<a>) : list<a>`
Arguments	list1: The list to subtract from when an element is not found in list2. list2: The list to find duplicates in, in order to keep elements of list1.
Category	List
Context	All Returns the elements of a first list that appear in a second list.

Example Expression	Expected Result
intersection({1,2,3},{2,3,4})	{}
intersection({5,5,5},{4,5,6})	{5,5,5}
intersection({4,5,6},{6,6,6})	{6}
intersection({},{1,2})	{}
intersection({1,2},{})	{}
intersection({},{})	{}

intersectRanges

Syntax	intersectRanges(list<range<a>>) : range<a>
Arguments	ranges: A list of ranges
Category	Range
Context	All Returns the intersection of 2 or more ranges, or null if the ranges do not intersect.

Example Expression	Expected Result
intersectRanges(range(1, 10), range(5, 15))	range(5, 10)
intersectRanges(range(1, 10), range(11, 20))	null
intersectRanges(range(1, 10), range(5, 15), range(8, 9))	range(8, 9)
intersectRanges(range(1, 10), range(5, 15), range(12, 20))	null
intersectRanges(range(null, 10), range(10, null))	range(10, 10)

isElem

Syntax	`isElem(a,list<a>) : bool`
Arguments	value: the item to check for membership in the given list. list: The list that might contain the item.
Category	List
Context	All Checks if the given element is a member of the given list.

Example Expression	Expected Result
isElem("a",{"a","b","c"})	True
isElem("e",{"a","b","c"})	False

isEmpty

Syntax	isEmpty(a) : bool
Arguments	value: The value to check
Category	Value
Context	All Returns True if the argument evaluated is determined to be empty. Values that can be empty are strings, lists, sets, tuples, attributes, and null.

Example Expression	Expected Result
isEmpty("")	true
isEmpty("abc")	false
isEmpty({})	true
isEmpty({1,2,3})	false
isEmpty(set())	true
isEmpty(set(1,2,3))	false
isEmpty(tuple())	false
isEmpty(tuple(1,2,3))	false
isEmpty(attribute(NULL, NULL))	true
isEmpty(attribute("FIXED", 1))	false
isEmpty(null)	true

isError

Syntax	`isError(a) : bool`
Arguments	value: The value to check
Category	Error, Value
Context	All Returns true if the argument is an error value.

Example Expression	Expected Result
isError(5)	False
isError(5 / 0)	True

isGroupingBy

Syntax	`isGroupingBy(a) : bool`
Arguments	grouping: A reference to check against the groupings in the current evaluation context.
Category	Reference
Context	field, query Indicates whether a given value is being grouped on in the current context.

Example Expression	Expected Result
isGroupingBy([Country])	True

isSimilarValue

Syntax	isSimilarValue(a,number,a,list<number>) : bool
Arguments	subject: The subject for which similar values should be retrieved nSimilar: The number of similar values to be included compareKey: The subject key value to which the comparison is made features: The feature properties on which the comparison is performed
Category	Reference
Context	field, query, report Checks whether the current instance of a subject is one of the N most similar values to a reference value based on the features specified. The feature properties can be specified directly for continuous number values. The similarityFeature() function can also be used for specifying features to give weighting values, or for specifying non-numeric or discrete value feature properties.

Example Expression	Expected Result
isSimilarValue([Country], 5, 'France', [Country].[Population], [Country].[GDP])	false
isSimilarValue([Country], 5, 'France', [Country].[GDP], similarityFeature([Country].[Population], True, 1.0), similarityFeature([Country].[Language], False, 0.5))	false

join

Syntax	`join(text,list<a>) : text`
Arguments	separator: The separator used in joining list elements. list: The list containing elements to be joined.
Category	List
Context	All Joins the elements in the list with the separator and returns the result in a single string.

Example Expression	Expected Result
`join(",", {"a","b"})`	“a,b”
join("–", 1.0,2.0)	“1–2”

joinNonEmpty

Syntax	`joinNonEmpty(text,list<a>) : text`
Arguments	separator: The separator used in joining list elements. list: The list containing elements to be joined.
Category	List
Context	All Joins the non-null/non-empty elements of the list with the separator and returns the result in a single string. If the list does not contain any null or empty value, this function behaves the same as the join function. Note that the generated text does not include empty strings, empty sets, empty maps, empty tuples and empty lists or nulls.

Example Expression	Expected Result
joinNonEmpty(",", {"a", NULL, "b", ""})	"a,b"
joinNonEmpty("-", {}, {1, 2}, {3})	"[1, 2]-[3]"
joinNonEmpty("-", set(), set(1, 2))	"{1, 2}"

joinNonNull

Syntax	`joinNonNull(text,list<a?) : text`
Arguments	separator: The seaparator used in joining list elements. list: The list containing elements to be joined.
Category	list
Context	All Joins the non-null elements in the list with the separator and returns the result in a single string. If the list does not contain any null value, this function behaves the same as the join function.

Example Expression	Expected Result
joinNonNull(",", {"a", NULL, "b", NULL})	"a,b"
joinNonNull("-", 1.0,2.0)	"1-2"

kpiActual

Syntax	kpiActual(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the actual value for the KPI for the configured time period.

Example Expression	Expected Result
kpiActual([SampleKPI])	80

kpiActualDelta

Syntax	kpiActualDelta(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the raw difference between the current KPI actual value and the value from the preceding time period.

Example Expression	Expected Result
kpiActualDelta([SampleKPI])	-10

kpiActualDeltaPercent

Syntax	kpiActualDeltaPercent(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the percentage difference between the current KPI actual value and the value from the preceding time period.

Example Expression	Expected Result
kpiActualDeltaPercent([SampleKPI])	-11.1%

kpiGoal

Syntax	kpiGoal(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the goal value for the KPI correponding to the current context.

Example Expression	Expected Result
kpiGoal([SampleKPI])	100

kpiNotificationContact

Syntax	kpiNotificationContact(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the contact value for the KPI corresponding to the current context, if specified.

Example Expression	Expected Result
kpiNotificationContact([SampleKPI])	12345

kpiPriorActual

Syntax	kpiPriorActual(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the actual value for the KPI for the time period preceding the current configured period.

Example Expression	Expected Result
kpiActualPrior([SampleKPI])	90

kpiScore

Syntax	kpiScore(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the score for the KPI for the configured time period and current context.

Example Expression	Expected Result
kpiScore([SampleKPI])	0.8

kpiScoreDelta

Syntax	kpiScoreDelta(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the raw difference between the current KPI score value and the value from the preceding time period.

Example Expression	Expected Result
kpiScoreDelta([SampleKPI])	-0.1

kpiTimePeriodDisplay

Syntax	kpiTimePeriodDisplay(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the relative time period display text for the KPI.

Example Expression	Expected Result
kpiTimePeriodDisplay([SampleKPI])	Last 30 Day(s)

kpiTimePeriodName

Syntax	kpiTimePeriodName(number) : text
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the symbolic name for the KPI time period.

Example Expression	Expected Result
kpiTimePeriodName([SampleKPI])	Day

kpiTimePeriodRange

Syntax	kpiTimePeriodRange(number) : number
Arguments	kpi: The measure on which the KPI is based
Category	KPI
Context	field, query, report Returns the number of previous KPI time periods.

Example Expression	Expected Result
kpiTimePeriodRange([SampleKPI])	7

last

Syntax	last(list<a>) : a
Arguments	list: The list from which to take the last element
Category	List
Context	All Returns the last element of the list. If the list is empty, returns null.

Example Expression	Expected Result
last({1,2,3,4})	4
last({1})	1
last({})	null

left

Syntax	`left(text,number) : text`
Arguments	text: The original text from which to extract a subsequence. numberOfChars: The number of characters to extract.
Category	Text
Context	All Returns the leftmost characters from the text argument.

Example Expression	Expected Result
left("huge", 3)	hug
left("Brown fox", 0)
left("Jump", 200)	Jump

len

Syntax	`len(list<a>) : number`
Arguments	list: The list for which to calculate the length
Syntax	`len(text) : number`
Arguments	text: The text for which to calculate the length
Category	List
Context	All Returns the length of the list.

Example Expression	Expected Result
len({"a","b","c"}))	3

lessThan

Syntax	lessThan(a,a) : bool
Arguments	val1: The first value to compare val2: The second value to compare
Category	Comparison
Context	All Returns True if the first value is less than the second value.

Example Expression	Expected Result
5 < 10	true
5 < 5	false

lessThanEquals

Syntax	lessThanEquals(a,a) : bool
Arguments	val1: The first value to compare val2: The second value to compare
Category	Comparison
Context	All Returns True if the first value is less than or equal to the second value.

Example Expression	Expected Result
5 <= 10	true
5 <= 5	true

lime

Syntax

lime() : color

Category

Color

Context

All

The color lime.

lines

Syntax	`lines(text) : list<text>`
Arguments	text: The text to be split to lines.
Category	Text
Context	All Splits a text into a list of lines. Empty lines are ignored and not appended into the list.

Example Expression	Expected Result
lines("First fox" & char(10) & "Second fox")	{'First fox', 'Second fox'}
lines(unlines("First fox", "Second fox"))	{'First fox', 'Second fox'}

locale

Syntax	locale() : text
Arguments
Category	Localization
Context	field, query, report Returns the name of the current user’s locale

Example Expression	Expected Result
locale	en_US

localize

Syntax	`localize(text) : text`
Arguments	key: The key to be looked up in the translation table.
Category	Localization
Context	All Returns the formatted text for the given value and formatting attributes set.

Example Expression	Expected Result
localize("please translate me into my locale")	gelieve te vertalen me in mijn locale

lookupAttribute

Syntax	`lokupAttribute(attributes,text) : a`
Arguments	attributes: The set of attributes to check. attrName: The name of the attribute.
Syntax	`lookupAttribute(attributes,text,number) : a`
Arguments	attributes: The set of attributes to check. attrName: The name of the attribute. currentval: The current value to be used to resolve any conditions in the attributes.
Category	Attributes
Context	All Looks up the value of an attribute (by name) in the set of content attributes. Optionally, a current value may also be specified for resolving conditions in the attributes set.

Example Expression	Expected Result
`lookupAttribute(<A 1; B 2>, 'B')`	2
lookupAttribute(<A 1; B 2; RANGE 1:10 <C 3> >, 'C', 5)	3

lookupProperty

Syntax	lookupProperty(a,b) : a lookupProperty(a,b,c) : a lookupProperty(a,b,c,d) : a
Arguments	property: The subject property for which the value should be retrieved keyValue: The value of the subject key for which the property will be retrieved keyValue1: The value of the first subject key for which the property will be retrieved keyValue2: The value of the second subject key for which the property will be retrieved keyValue3: The value of the third subject key for which the property will be retrieved
Category	Reference
Context	field, query, report Returns the value of a property where the subject key or keys has the given values.

Example Expression	Expected Result
lookupProperty([Employee].[StartDate], 'E12345')	2014/02/11
lookupProperty([Country].[Language], coalesce([Cust].[ShipToCountry], [Cust].[ResidenceCountry]))	FR
lookupProperty([LocalizedCountry].[LocName], [Employee].[CountryOfBirth], 'en')	France

lookupUserAttribute

Syntax	lookupUserAttribute(text) : text
Arguments	userAttrName: The name of the user attribute
Category	Attributes
Context	field, query, report Looks up the value of a user attribute by name and returns it as text. It returns null if the user attribute does not exist.

Example Expression	Expected Result
lookupUserAttribute("indicee.user_attribute")	5
lookupUserAttribute("indicee.missing_attribute")	null

lower

Syntax	`lower(text) : text`
Arguments	text: The text to convert to lower case.
Category	Text
Context	All Returns the given text converted into lower case.

Example Expression	Expected Result
lower("The Quick FOX")	the quick fox

makeAttributes

Syntax	makeAttributes(text) : attributes
Arguments	text: The text form of the attributes data
Category	Attributes
Context	All Constructs a set of content attributes (fixed and/or conditional) from the specified text form of the attributes data.

Example Expression	Expected Result
makeAttributes("BOLD")	<BOLD>
makeAttributes("FORECOLOR " & [RegionColor])	<BOLD; FORECOLOR color(255, 0, 0)>

maroon

Syntax

maroon() : color

Category

Color

Context

All

The color maroon.

matches

Syntax	`matches(text,text) : bool`
Arguments	regex: The regular expression pattern. text: The body text to test for a match with the given pattern.
Category	Text
Context	All Determines whether a given body of text exactly matches a given pattern.

Example Expression	Expected Result
matches("fo*bar", "foobar")	true (1)
matches("fo*bar", "fbar")	true (1)
matches("fo*bar", "fubar")	false (0)

max

Syntax	`max(a) : a`
Arguments	val: The values to aggregate.
Category	Aggregation
Context	field, query, report Returns the largest value recorded for a specified field.

Example Expression	Expected Result
max([Sales])	43000.5

maxOfList

Syntax	`maxOfList(list<a>) : a`
Arguments	list: The list of values to find the maximum.
Category	List
Context	All Returns the maximum value in a given list. The list can be passed to the function as a literal list enclosed in braces or as a sequence of arguments.

Example Expression	Expected Result
maxOfList({1,2,3})	3
maxOfList(1,2,3)	3
maxOfList('c', 'a', 't')	't'

mid

Syntax	`mid(text,number,number) : text`
Arguments	text: The original text from which to extract a subsequence. index: The character position of the first character to extract. length: The length of the text to extract.
Category	Text
Context	All Returns a subsequence of text from within a larger block of text.

Example Expression	Expected Result
mid("The quick fox", 7, 3)	ick
mid("The quick fox", 5, 5)	quick
mid("Brown fox", 20, 0)
mid("Jump", 2, 10)	ump

min

Syntax	`min(a) : a`
Arguments	val: The values to aggregate.
Category	Aggregation
Context	field, query, report Returns the smallest value recorded for a specified field.

Example Expression	Expected Result
min([Sales])	10.0

minOfList

Syntax	`minOfList(list<a>) : a`
Arguments	list: The list of values to find the maximum.
Category	List
Context	All Returns the minimum value in a given list. The list can be passed to the function as a literal list enclosed in braces or as a sequence of arguments.

Example Expression	Expected Result
minOfList({1,2,3})	1
minOfList(1,2,3)	1

minute

Syntax	`minute(time) : number`
Arguments	time: The time from which to extract the minute value
Category	Time
Context	All Returns the minute portion of the given date-time value.

Example Expression	Expected Result
minute([BonusDate])	40

mod

Syntax	`mod(number,number) : number`
Arguments	num: The number to be divided (numerator). denom: The number to divide by (denominator).
Category	Numeric
Context	All Returns the modulo of a quotient of the two arguments (number/divisor). The result is always a number between 0 and the second operand. If both operands are positive, the result is identical to the rem (remainder) operator. Division by zero returns an error.

Example Expression	Expected Result
mod(3, 2)	1
mod(4, 2)	0
mod(5, –2)	–1
mod(–5, 3)	1
mod(–5, –2)	–1
mod(5, 2.4)	0.2

month

Syntax	`month(time) : number`
Arguments	time: The time from which to extract the month value
Category	Time
Context	All Returns the month portion of the given date-time value.

Example Expression	Expected Result
month([BonusDate])	8

multiply

Syntax	`multiply(number,number) : number`
Arguments	val1: The first value to multiply. val2: The second value to multiply.
Category	Numeric
Context	All Multiplies two number values. If either value is null, then the result will be null.

Example Expression	Expected Result
2 * 3	6
2 * NULL	NULL

navy

Syntax

navy() : color

Category

Color

Context

All

The color navy.

negate

Syntax	negate(number) : number
Arguments	num: The number to be negated
Category	Numeric
Context	All Negates the given number, changing positive values to negative values and vice versa.

Example Expression	Expected Result
negate(2)	-2
negate(0)	0

newLine

Syntax	newLine() : text
Arguments	—
Category	text
Contexts	All Inserts a new line.

Example Expression	Expected Result
'ab' & newLine & 'c'	{{ab c}}

not

Syntax	not(bool) : bool
Arguments	bool: The boolean value to negate.
Category	Logical
Context	All Returns the logical negation of the boolean argument.

Example Expression	Expected Result
not(2 = 3)	true (1)
not (1 = 1)	false (0)

notEquals

Syntax	notEquals(a,a) : bool
Arguments	val1: The first value to compare val2: The second value to compare
Category	Comparison
Context	All Returns True if the first value is not equal to the second value. Note that comparing against null is allowed.

Example Expression	Expected Result
5 <> 10	True
5 <> 5	false
Null <> 5	true
Null <> Null	false

now

Syntax	now() : time
Arguments	—
Category	Time
Context	field, query, report Returns the current date-time. This can be combined with other date functions.

Example Expression	Expected Result
year(now())	2021

nullBoolean

Syntax	nullBoolean() : bool
Arguments
Category	Value
Context	All A Null value with the value type of Boolean.

Example Expression	Expected Result
nullBoolean	null

nullConditionAttribute

Syntax	nullConditionAttribute(text,a) : attributes
Arguments	attrName: The name of the attribute. attrValue: The value of the attribute.
Category	Attributes
Context	All Constructs a content attribute containing a single attribute with an “Is Null” condition.

Example Expression	Expected Result
nullConditionAttribute("FIXED", 1)	<Null<FIXED 1>>

nullIf

Syntax	nullIf(a,a) : a
Arguments	value: The value to check. comparison: The value for which a null value should be returned.
Category	Value
Context	All Returns the value of the first argument, except when the value is equal to the second argument, in which case Null is returned.

Example Expression	Expected Result
nullIf(5, 10)	5
nullIf(5, 5)	NULL

nullNumber

Syntax	nullNumber() : number
Arguments
Category	Value
Context	All A null value with the value type of Number.

Example Expression	Expected Result
nullNumber	null

nullText

Syntax	nullText() : text
Arguments
Category	Value
Context	All A null value with the value type of Text.

Example Expression	Expected Result
nullText	null

nullTime

Syntax	nullTime() : time
Arguments
Category	Value
Context	All A null value with the value type of Date/Time.

Example Expression	Expected Result
nullTime	null

numericValue

Syntax	numericValue(number) : number numericValue(bool) : number numericValue(text) : number numericValue(time) : number
Arguments	value: The value to be converted to a number.
Category	Numeric
Context	All Returns the numeric value of the given argument, if applicable. If conversion to a number is not possible, Null is returned. A date passed to this function is converted into the number of seconds in the Unix epoch (the number of seconds since 1970-01-01T00:00:00Z).

Example Expression	Expected Result
numericValue(3)	3
numericValue('123.45')	123.45
numericValue('hello')	null

offsetTimePeriodRange

Syntax	offsetTimePeriodRange(time,text,number) : range<time>
Arguments	timeval: The starting point in time shiftPeriodName: The name of the time period by which the time will be shifted offset: Number of time periods by which the time will be shifted
Category	Time
Context	All Returns the time range corresponding to the time period offset by N periods from the specified starting time.

Example Expression	Expected Result
offsetTimePeriodRange(now, "Year", -1)	(2011/01/01, 2012/01/01)

olive

Syntax

olive() : color

Category

Color

Context

All

The color olive.

or

Syntax	or(bool,bool) : bool
Arguments	bool1: The first Boolean argument bool2: The second Boolean argument
Category	Logical
Context	All Returns True if either of the argument values are true, and otherwise returns False.

Example Expression	Expected Result
or(true, false)	true (1)
or(true, true)	true (1)

orange

Syntax

orange() : color

Category

Color

Context

All

The color orange.

pair

Syntax	pair(a,b) : tuple<a, b>
Arguments	val1: The first value val2: The second value
Category	Tuple
Context	All Constructs a pair of values.

Example Expression	Expected Result
pair(1, 'abc')	tuple(1, 'abc')

percentOfTotal

Syntax	percentOfTotal(a,b) : a percentOfTotal(a,b,c) : a percentOfTotal(a,b,c,d) : a
Arguments	val: The expression to find the percentage of which is in the specified groupings. grouping1: A grouping to include in the numerator of the percentage. grouping2: A second grouping to include in the numerator of the percentage. grouping3: A third grouping to include in the numerator of the percentage.
Category	Reference
Context	field, query The percentage of the first argument that is inside the groupings specified by the other arguments.

Example Expression	Expected Result
percentOfTotal([Sales], [Country])	0.1
percentOfTotal([Sales], [Country], [Product], [Time])	0.2

positiveNum

Syntax	positiveNum(number) : number
Arguments	num: The number to be returned
Category	Numeric
Context	All Returns a positive number.

Example Expression	Expected Result
positiveNum(2)	2
positiveNum(0)	0

power

Syntax	power(number,number) : number
Arguments	base: The value whose power is to be taken. exponent: The value specifying the exponent in the exponentiation.
Category	Numeric
Context	All Raises the first argument to the power of the second argument.

Example Expression	Expected Result
power(10, 3)	1000
power(16, 0.5)	4
power(10, –2)	0.01

productOfList

Syntax	productOfList(list<number>) : number
Arguments	list: The list of numbers
Category	List
Context	All Returns the product of the numbers in a given list.

Example Expression	Expected Result
productOfList(2, 2)	4
productOfList({2, 2, 2})	8
productOfList({2, 4, 2})	16
productOfList({})	1

proper

Syntax	proper(text) : text
Arguments	text: The text to convert to headline case.
Category	Text
Context	All Returns the given text with the first letter of every word capitalized.

Example Expression	Expected Result
proper("the firm")	The Firm

purple

Syntax

purple() : color

Category

Color

Context

All

The color purple.

quarter

Syntax	quarter(time) : number
Arguments	time: The time from which to extract the quarter.
Category	Time
Context	All Returns the quarter of the year of the given date-time value.

Example Expression	Expected Result
quarter([BonusDate])	3

randBetween

Syntax	randBetween(number,number) : number
Arguments	lower: The minimum number, inclusive upper: the maximum number, inclusive
Category	Random
Context	All Generates a random number in a closed interval: [lower, upper].

Example Expression	Expected Result
randBetween(1, 10)	7

range

Syntax	range(a,a) : range<a> range(a,bool,a,bool) : range<a>
Arguments	start: The starting value in the range, or null if no lower bound end: The ending value in the range, or null if no upper bound startInclusive: True if the start of the range (if any) is inclusive endInclusive: True if the end of the range (if any) is inclusive
Category	Range
Context	All Constructs a range between 2 values. A null value can be used to indicate no bound at one or both endpoints. Optionally, the start and end values can be followed by flags indicating whether they are inclusive (True) or exclusive (False). By default, the range endpoints are inclusive.

Example Expression	Expected Result
range(1, 10)	1 <= x <= 10
range(NULL, 5)	x <= 5
range(0, True, 5, False)	0 <= x < 5
range(0, False, null, True)	x > 0

rangeConditionAttribute

Syntax	rangeConditionAttribute(text,a,number,number) : attributes
Arguments	attrName: The name of the attribute. attrValue: The value of the attribute. rangeStart: The lower-bound of the range condition, or Null for no lower-bound. rangeEnd: The upper-bound of the range condition, or Null for no upper-bound.
Category	Attributes
Context	All Constructs a content attribute containing a single attribute with a range condition. The range is bounded by the start and/or end value inclusive unless they are null. If they are null, no bound is applied at that end.

Example Expression	Expected Result
rangeConditionAttribute("FIXED", 1, 3, 5)	<Range 3:5<FIXED 1>>
rangeConditionAttribute("FIXED", 1, 3, Null)	<Range 3:<FIXED 1>>

rangeEnd

Syntax	rangeEnd(range<a>) : a
Arguments	range: The value range
Category	Range
Context	All Returns the ending value of a range, or Null if there is no upper bound.

Example Expression	Expected Result
rangeEnd(range(1, 10))	10
rangeEnd(range(10, null))	null

rangeIncludesEnd

Syntax	rangeIncludesEnd(range<a>) : bool
Arguments	range: The value range
Category	Range
Context	All Returns whether the ending value of a range (if any) is inclusive (True) or exclusive (False).

Example Expression	Expected Result
rangeIncludesEnd(range(1, 10))	true
rangeIncludesEnd(range(10, null))	false
rangeIncludesEnd(range(1, False, 10, False))	false

rangeIncludesStart

Syntax	rangeIncludesStart(range<a>) : bool
Arguments	range: The value range
Category	Range
Context	All Returns whether the starting value of a range (if any) is inclusive (True) or exclusive (False).

Example Expression	Expected Result
rangeIncludesStart(range(1, 10))	true
rangeIncludesStart(range(null, 10))	false
rangeIncludesStart(range(1, False, 10, False))	false

rangesIntersect

Syntax	rangesIntersect(list<range<a>>) : bool
Arguments	ranges: A list of ranges
Category	Range
Context	All Returns whether 2 or more ranges intersect.

Example Expression	Expected Result
rangesIntersect(range(1, 10), range(5, 15))	true
rangesIntersect(range(1, 10), range(11, 20))	false
rangesIntersect(range(1, 10), range(5, 15), range(8, 9))	true
rangesIntersect(range(1, 10), range(5, 15), range(12, 20))	false
rangesIntersect(range(null, 10), range(10, null))	true

rangeStart

Syntax	rangeStart(range<a>) : a
Arguments	range: The value range
Category	Range
Context	All Returns the starting value of a range, or null if there is no lower bound.

Example Expression	Expected Result
rangeStart(range(1, 10))	1
rangeStart(range(null, 10))	null

red

Syntax

red() : color

Category

Color

Context

All

The color red.

regexSplitAll

Syntax	regexSplitAll(text,text) : list<text>
Arguments	text: The text to be split. sepRegex: The regular expression for the separator.
Category	Text
Context	All Splits the text based on the regex separator pattern specified.

Example Expression	Expected Result
regexSplitAll("A;B;C", ";")	[A, B, C]

rem

Syntax	rem(number,number) : number
Arguments	num: The number to be divided (the numerator). denom: The number to divide by (the denominator).
Category	Numeric
Context	All Returns the remainder of an integer division of the two arguments (number/divisor). The result always has the same sign as the first operand. For operands with different signs, the result of this function is different from the one returned by the mod function. If both operands are positive, the result is identical to the mod (modulo) operator.

Example Expression	Expected Result
rem(3, 2)	1
rem(4, 2)	0
rem(5, –2)	–1
rem(–5, 3)	1
rem(–5, –2)	–1
rem(5, 2.4)	0.2
rem(5.5, 2.4)	0.7
rem(4, 0)	ERR{DivideByZero}

removeAttribute

Syntax	removeAttribute(attributes,text) : attributes
Arguments	attributes: The set of content attributes (conditional and/or unconditional). AttrName: The name of the attribute to be removed.
Category	Attributes
Context	All Removes any references to the specified attribute from the content attributes (with or without conditions).

Example Expression	Expected Result
removeAttribute(<FIXED 1; FONT_FACE "Arial">, "FIXED")	<FONT_FACE "Arial">
removeAttribute(<FIXED 1; Error<FONT_FACE "Arial">>, "FONT_FACE")	<FIXED 1>

removeDuplicates

Syntax	removeDuplicates(list<a>) : list<a>
Arguments	list: The list to remove duplicates from.
Category	List
Context	All Removes duplicate elements from a list. The first occurrence of each element is the one that remains.

Example Expression	Expected Result
removeDuplicates({1,2,2,4})	{1,2,4}
removeDuplicates({3,3,6,6,9,9})	{3,6,9}
removeDuplicates({3,3,6,1,6,9,2,9})	{3,6,1,9,2}
removeDuplicates({1,5,10})	{1,5,10}
removeDuplicates({})	{}

repeat

Syntax	repeat(number,a) : list<a>
Arguments	number: The number of times to repeat the provided value value: The value to be repeated into a list
Category	List
Context	All Returns the given value repeated a specified number of times in a list.

Example Expression	Expected Result
repeat(4, "hello")	{"hello","hello","hello","hello"}
repeat(1, "once")	{"once"}
repeat(0, "none")	{}

replace

Syntax	replace(text,text,text) : tex
Arguments	regex: The regular expression pattern. replacement: The replacement text. text: The text to be replaced.
Category	Text
Context	All Returns a copy of text with all matches of regex pattern replaced by the replacement text.

Example Expression	Expected Result
replace(".at", "cat", "bat,fat,sit")	cat,cat,sit

replicate

Syntax	replicate(number,text) : text
Arguments	ncopies: The number of times to replicate the text. text: The text to replicate.
Category	Text
Context	All Replicates the given text for the given number of times and concatenates them all into a new text value.

Example Expression	Expected Result
replicate (3,"foo")	"foofoofoo"

reverse

Syntax	reverse(list<a>) : list<a>
Arguments	list: The list to reverse.
Category	List
Context	All Reverses a list.

Example Expression	Expected Result
reverse({1,2,3,4,5})	{5,4,3,2,1}
reverse({9})	{9}
reverse({})	{}

right

Syntax	right(text,number) : text
Arguments	text: The original texst from which to extract a subsequence. numberOfChars: The number of characters to extract.
Category	Text
Context	All Returns the rightmost characters from the text argument.

Example Expression	Expected Result
right("ushers", 4)	hers

round

Syntax	round(number) : number round(number,number) : number
Arguments	value: The numeric value to be rounded. nDecPlaces: The number of decimal places to which the value is rounded.
Category	Numeric
Context	All Returns the rounded value of the argument. Values that are halfway between two whole numbers are rounded to the even value. An optional second argument determines the decimal place to which the function rounds. If the number of decimal places is negative, the rounding applies to the left of the decimal point.

Example Expression	Expected Result
round(1.9)	2
round(1.5)	2
round(2.5)	2
round(–1.9)	–2
round(–1.5)	–2
round(123.456, 1)	123.5
round(123.456, –1)	120

second

Syntax	second(time) : number
Arguments	time: The time from which to extract the seconds.
Category	Time
Context	All Returns the seconds of the given date-time value.

Example Expression	Expected Result
second([BonusDate])	11

set

Syntax	set(list<a>) : set<a>
Arguments	list: The list of values to be included in the set.
Category	Set
Context	All Returns the set of all values in a given list, including Null and Error values.

Example Expression	Expected Result
set({4,3,2,1,2,3})	1,2,3,4
set(1,null,2)	null,1,2
set('a', 'b', 'b', 'a')	'a', 'b'

setDifference

Syntax	setDifference(set<a>,list<set<a>>) : set<a>
Arguments	set: The starting set of values setsToRemove: The sets to be removed
Category	Set
Context	All Removes the values from subsequent sets from the first set.

Example Expression	Expected Result
setDifference(set(1, 2, 3, 4), set(2, 5))	1, 3, 4
setDifference(set(1, 2, 3), set(3, 4, 5), set(0, 2, 4, 6, 8))	1

setIntersection

Syntax	setIntersection(list<set<a>>) : set<a>
Arguments	sets: The sets to be intersected
Category	Set
Context	All Returns the values which exist in all of the provided sets.

Example Expression	Expected Result
setIntersection(set(1, 2, 3, 4), set(2, 5))	2
setIntersection(set(1, 2, 3, 4), set(2, 3, 4, 5), set(0, 2, 4, 6, 8))	2, 4

setSize

Syntax	setSize(set<a>) : number
Arguments	set: The set of values
Category	Set
Context	All Returns the number of values in a set.

Example Expression	Expected Result
setSize(set({4,3,2,1,2,3}))	4
setSize(set(1,null,2))	3
setSize(set('a', 'b', 'b', 'a'))	2

setToList

Syntax	setToList(set<a>) : list<a>
Arguments	set: The set of values
Category	Set
Context	All Returns a list corresponding to values in a set.

Example Expression	Expected Result
setToList(set({4,3,2,1,2,3}))	1,2,3,4
setToList(set(1,null,2))	null,1,2
setToList(set('a', 'b', 'b', 'a'))	'a', 'b'

setUnion

Syntax	setUnion(list<set<a>>) : set<a>
Arguments	sets: The sets to be unioned
Category	Set
Context	All Returns the values which exist in any of the provided sets.

Example Expression	Expected Result
setUnion(set(1, 2, 3, 4), set(2, 5))	1, 2, 3, 4, 5
setUnion(set(1, 2, 3), set(2, 3, 4), set(0, 2, 4, 6, 8))	0, 1, 2, 3, 4, 6, 8

setValueGreaterThan

Syntax	setValueGreaterThan(a,set<a>) : a
Arguments	val: The value to look up in the set set1: The set of values
Category	Set
Context	All Returns the largest set value larger than the specified value, if any.

Example Expression	Expected Result
setValueGreaterThan(3, set(1, 2, 3, 4))	4
setValueGreaterThan(4, set(1, 3, 5, 7))	5
setValueGreaterThan(4, set(1, 2, 3, 4))	null

setValueGreaterThanEqual

Syntax	setValueGreaterThanEqual(a,set<a>) : a
Arguments	val: The value to look up in the set set1: The set of values
Category	Set
Context	All Returns the largest set value larger than or equal to the specified value, if any.

Example Expression	Expected Result
setValueGreaterThanEqual(3, set(1, 2, 3, 4))	3
setValueGreaterThanEqual(4, set(1, 3, 5, 7))	5
setValueGreaterThanEqual(7, set(1, 2, 3, 4))	null

setValueLessThan

Syntax	setValueLessThan(a,set<a>) : a
Arguments	val: The value to look up in the set set1: The set of values
Category	Set
Context	All Returns the largest set value smaller than the specified value, if any.

Example Expression	Expected Result
setValueLessThan(3, set(1, 2, 3, 4))	2
setValueLessThan(3, set(1, 3, 5, 7))	3
setValueLessThan(1, set(1, 2, 3, 4))	null

setValueLessThanEqual

Syntax	setValueLessThanEqual(a,set<a>) : a
Arguments	val: The value to look up in the set set1: The set of values
Category	Set
Context	All Returns the largest set value smaller than or equal to the specified value, if any.

Example Expression	Expected Result
setValueLessThanEqual(3, set(1, 2, 3, 4))	3
setValueLessThanEqual(4, set(1, 3, 5, 7))	3
setValueLessThanEqual(0, set(1, 2, 3, 4))	null

shiftTime

Syntax	shiftTime(time,text,number) : time
Arguments	timeVal: The time value to be shifted periodName: The name of the time period to shift by nPeriods: The number of time periods to shift the time value
Category	Time
Context	All Shifts a time value by a number of intervals for a given time period. A negative shift value can be used to shift the time to an earlier time. The shifting is sensitive to the user’s time zone (where the shift crosses a DST boundary).

Example Expression	Expected Result
shiftTime(toDate(2019,7,15), 'Year', 2)	2021/07/15
shiftTime(toDate(2021,2,15), 'Month', -1)	2021/01/15

sign

Syntax	sign(number) : number
Arguments	value: The numeric value for which the sign will be returned.
Category	Numeric
Context	All Returns a value that represents the sign of a given number. It returns –1 for negative numbers, 0 for zero, and 1 for positive numbers.

Example Expression	Expected Result
sign(100)	1
sign(0)	0
sign(–100)	–1

silver

Syntax

silver() : color

Category

Color

Context

All

The color silver.

similarityFeature

Syntax	similarityFeature(a,bool,number) : number
Arguments	featureProp: The feature property on which the feature is based isContinuous: True for continuous numeric values (compared for similarity), False for discrete values (compared by equality only) weighting: A weighting factor which gives more influence to features with higher weighting values (default = 1)
Category	Reference
Context	field, query, report Specifies the configuration for a feature to be used when querying similar values.

Example Expression	Expected Result
similarityFeature([Country].[Population], True, 1.0)	null
similarityFeature([Country].[Language], False, 0.5)	null

sort

Syntax	sort(list<a>) : list<a>
Arguments	list: The list to sort.
Category	List
Context	All Sorts a list in ascending order.

Example Expression	Expected Result
sort({2,5,4,1,3})	{1,2,3,4,5}
sort({"cat","abc","battle"})	{"abc", "battle", "cat"}
sort({})	{}

split

Syntax	split(text,text,number) : text
Arguments	text: The original text to split. delimiter: The separator text used to determine when to split. index: The index of the component of the split to return.
Category	Text
Context	All Returns a component of the specified text, partitioned according to some separator text.

Example Expression	Expected Result
split("foo.bar.baz", ".", 3)	baz
split("foo.bar.baz", ".", 4)

splitAt

Syntax	splitAt(number,list<a>) : list<list<a>>
Arguments	index: The index to split the given list (1-based) list: The list to discard the last value from
Category	List
Context	All Returns the given list split at the index number.

Example Expression	Expected Result
splitAt(3, {"hello", "goodbye", "hello", "goodbye"})	{{"hello", "goodbye"}, {"hello", "goodbye"}}
splitAt(3, {1,2,3,4,5})	{{1, 2}, {3, 4, 5}}
splitAt(2, {1,2})	{{1}, {2}}
splitAt(4, {1,2,3})	{{1, 2, 3}, {}}
splitAt(10, {})	{{}, {}}

sqrt

Syntax	sqrt(number) : number
Arguments	value: The numeric value for which the square root will be computed.
Category	Numeric
Context	All Returns the positive square root of the given value.

Example Expression	Expected Result
sqrt(9)	3

startsWith

Syntax	startsWith(list<a>,list<a>) : bool
Arguments	prefix: The value or list to be checked for at the beginning of the list. list: The list that might contain the prefix list.
Category	List
Context	All Checks if the first list is a prefix of the second list.

Example Expression	Expected Result
startsWith({"a"},{"a","b"})	True
startsWith({"b"},{"a","b"})	False
startsWith({"a","b"},{"a","b","c"})	True

stdDev

Syntax	stdDev(number) : number
Arguments	val: The values to aggregate.
Category	Aggregation
Context	field, query, report Returns the arithmetic standard deviation of all of the data values for a given field.

Example Expression	Expected Result
stdDev([Sales])	2.0

sublist

Syntax	sublist(list<a>,number,number) : list<a>
Arguments	list: The list of values to create the sublist from. length: The length of the sublist. start: The index of the original list that will be the first element of the sublist.
Category	List
Context	All Returns the sublist from the index “start” to the index “end”.

Example Expression	Expected Result
sublist({1,2,3,4,5},2,3)	{2,3,4}
sublist({1,2,3,4,5},1,3)	{1,2,3}
sublist({1,2,3,4,5},3,3)	{3,4,5}
sublist({5,8},1,100)	{5,8}
sublist({8,9,1},-100,3)	{8,9,1}
sublist({},0,100)	{}

subscript

Syntax	subscript(number,list<a>) : a
Arguments	index: The index of the item in the list to return. list: The list to retrieve the item at the given index.
Category	List
Context	All Returns the element located at the provided index in the given list.

Example Expression	Expected Result
subscript(2, {"a","b","c"})	"b"

subtract

Syntax	subtract(number,number) : number
Arguments	val1: The base value val2: The value to subtract from the base value
Category	Numeric
Context	All Subtracts the second value from the first value. Note that subtracting a null value from a value returns the original value, and a value subtracted from a null value is the negative of the original value.

Example Expression	Expected Result
3 - 1	2
1 - NULL	1
NULL - 1	-1

sum

Syntax	sum(number) : number
Arguments	val: The values to aggregate.
Category	Aggregation
Context	field, query, report Returns the sum of all of the data values for a given field.

Example Expression	Expected Result
sum([Sales])	12345678.0

sumOfList

Syntax	sumOfList(list<number>) : number
Arguments	list: The list of values to find the sum.
Category	List
Context	All Returns the sum of the values in a given list. The list can be passed to the function as a literal list enclosed in braces or as a sequence of arguments.

Example Expression	Expected Result
sumOfList({1,2,3})	6.00
sumOfList(1,2,3)	6.00

tail

Syntax	tail(list<a>) : list<a>
Arguments	list: The list from which to drop the first element
Category	List
Context	All Returns the list without the first element. If the list is empty, returns an empty list.

Example Expression	Expected Result
tail({1,2,3,4})	{2,3,4}
tail({1})	{}
tail({})	{}

tails

Syntax	tails(list<a>) : list<list<a>>
Arguments	list: The list to derive suffixes from
Category	List
Context	All Returns all potential suffixes of the given list.

Example Expression	Expected Result
tails({"a","b","c"})	{{"a", "b", "c"}, {"b", "c"}, {"c"}, {}}
tails({1,2,3})	{{1, 2, 3}, {2, 3}, {3}, {}}
tails({1,2})	{{1, 2}, {2}, {}}
tails({1})	{{1}, {}}
tails({})	{{}}

take

Syntax	take(number,list<a>) : list<a>
Arguments	number: The number of values to take from the provided list list: The list to source values from
Category	List
Context	All Takes a specified number of elements from a list.

Example Expression	Expected Result
take(3, {"do", "not", "never", "do"})	{"do", "not", "never"}
take(3, {1,2})	{1, 2}
take(2, {})	{}

teal

Syntax

teal() : color

Category

Color

Context

All

The color teal.

text

Syntax	text(a) : text
Arguments	value: The value to convert into a string.
Category	Conversion
Context	All Returns the text value of the given value argument. This function accepts a value and converts it to text. If conversion to text is not possible, null is returned.

Example Expression	Expected Result
text(3.141592)	"3.141592"

timePartsPeriodRange

Syntax	timePartsPeriodRange(list<tuple<text, number>>) : range<time>
Arguments	timePeriodVals: Pairs of time period namesand period instance values.
Category	Time
Context	All Returns the time range defined by the provided pairs of time period names and periods instance values.

Example Expression	Expected Result
timePartsPeriodRange(pair('Year', 2014))	[2014/01/01, 2015/01/01)
timePartsPeriodRange(pair('Year', 2014), pair('Month', 2))	[2014/02/01, 2014/03/01)

timePeriodCaption

Syntax	timePeriodCaption(text,time) : text
Arguments	periodName: The name of the period to find. The time period name can be Year, Quarter, Month, DayOfMonth, Hour, Minute, or Second. time: The time value for which to look up the calendar period value.
Category	Time
Context	All Returns the name, as text, of a particular calendar period for the given time.

Example Expression	Expected Result
timePeriodCaption("Month", toDate(2012, 12, 31))	"Dec"
timePeriodCaption("Year", [BonusDate])	"2021"

timePeriodRange

Syntax	timePeriodRange(time,text) : range<time>
Arguments	time: The point in time timePeriod: The name of the time period
Category	Time
Context	All Returns the time range corresponding to the period containing the specified time. Null is returned if the specified time does not fall within an instance of the time period.

Example Expression	Expected Result
timePeriodRange(now, "Year")	[2012/01/01, 2013/01/01)

timePeriodValue

Syntax	timePeriodValue(text,time) : number
Arguments	periodName: The name of the period to find. The time period name can be Year, Quarter, Month, DayOfMonth, Hour, Minute, or Second. time: The time value for which to look up the calendar period value.
Category	Time
Context	All Returns the numeric value of the time period name that is specified for the given time.

Example Expression	Expected Result
timePeriodValue("Month", toDate(2021, 12, 31))	12
timePeriodValue("Year",[BonusDate])	2021

timePeriodValueCaption

Syntax	timePeriodValueCaption(text,number) : text
Arguments	periodName: The name of the period to find. The time period name can be Year, Quarter, Month, DayOfMonth, Hour, Minute, or Second. time: The time value for which to look up the calendar period value.
Category	Time
Context	All Returns the display value of the time component that is specified for a given numeric value of the time part.

Example Expression	Expected Result
timePeriodValueCaption("Month", 5)	May

timeShiftFilterPeriod

Syntax	timeShiftFilterPeriod(a,time,number) : a
Arguments	val: The expression to evaluate in the context of a leading or lagging time period timeField: The date value over which the time shifting is done shiftAmount: The number of whole units of the given filter time period to shift (negative for past, positive for future)
Category	Reference, Time
Context	field, query Returns the requested value, shifted by the specified number of periods corresponding to the value time filter. This function is useful for calculating period-to-period variances. The first parameter specifies the value to evaluate. The second parameter identifies the date value over which the time shifting is done. The third parameter is the number of whole units to shift by. Negative numbers shift into the past and positive numbers shift into the future.

Example Expression	Expected Result
timeShiftFilterPeriod([Sales], [Date], -1)	27

timeShiftValue

Syntax	timeShiftValue(a,time,text,number) : a
Arguments	val: The expression to evaluate in the context of a leading or lagging time period. timeField: The date reference (dimension) to use to find the correct value in time. shiftPeriod: The name of a time period (unit of time). shiftAmount: The number of whole units of the given shiftPeriod to shift (negative for past, positive for future).
Category	Reference, Time
Context	field, query Returns the requested value, shifted by the specified time offset. This function is useful for calculating period-to-period variances. The first parameter specifies the value to evaluate. The second parameter is the date reference dimension to use to find the correct value in time. The third parameter is the unit of time to shift by. It can be Year, Quarter, Month, DayOfMonth, Hour, Minute, or Second. The fourth parameter is the number of whole units to shift by. Negative numbers shift into the past and positive numbers shift into the future. For example, to determine the sales total from the preceding quarter you could use this formula: timeShiftValue([Sales], [saleDate], "Quarter", -1) To calculate the difference between the current period sales and the previous period sales, you could use this formula: [Sales] - timeShiftValue([Sales], [Date], "Year", -1)

Example Expression	Expected Result
timeShiftValue([Sales], [Date], "Year", -1)	27

timeZone

Syntax	timeZone() : text
Arguments
Category	Time
Context	field, query, report Returns the name of the current user’s time zone.

Example Expression	Expected Result
timeZone	America/Vancouver

toDate

Syntax	toDate(text,text) : time toDate(text,number,number,number,text) : time toDate(number,number,number,number,number,number) : time toDate(number,number,number,number,number) : time toDate(number,number,number) : time
Arguments	Syntax 1: format: The format pattern to match a date-time value from the text. dateTimeText: The date-time value represented as text. Syntax 2: regex: A regular expression with at least 3 captures for year, month, day. yearIndex: The index of the year capture in the regex. monthIndex: The index of the month capture in the regex. dayIndex: The index of the day capture in the regex. dateTimeText: The date-time value represented as text. Syntax 3: year: The year in the date. month: The 1-based month (1 = Jan, 2 = Feb, … 12 = Dec). day: The 1-based day of the month. hours: The 0-based hour of the day (0–23). minutes: The 0-based minute of the hour (0–59). seconds: The 0-based second of the minute (0–59). Syntax 4: year: The year in the date. month: The 1-based month (1 = Jan, 2 = Feb, … 12 = Dec). day: The 1-based day of the month. hours: The 0-based hour of the day (0–23). minutes: The 0-based minute of the hour (0–59). Syntax 5: year: The year in the date. month: The 1-based month (1 = Jan, 2 = Feb, … 12 = Dec). day: The 1-based day of the month.
Category	Time
Context	All Returns a date-time value obtained by interpreting a given non-date-time value. The toDate function has several forms. The year, month, day, and other parameters can be either passed explicitly or parsed from a string.

Example Expression	Expected Result
toDate("dd-MMM-yyyy HH:mm","13-aug-2019 14:12")	{Fri Aug 13 11:12:00 GMT-700 2019}
toDate(2019, 1, 31)	January 31, 2019
toDate(2019, 1, 31, 10, 4)	2019-1-31 10:04:00 UTC
toDate(2019, 1, 31, 10, 4, 34)	2010-1-31 10:04:34 UTC

trim

Syntax	trim(text) : text
Arguments	text: The original text.
Category	Text
Context	All Returns the given text with any leading and trailing white space removed.

Example Expression	Expected Result
trim(" Mary Jones ")	"Mary Jones"

triple

Syntax	triple(a,b,c) : tuple<a, b, c>
Arguments	val1: The first value val2: The second value val3: The third value
Category	Tuple
Context	All Constructs a triple of values.

Example Expression	Expected Result
triple(1, 'abc', False)	tuple(1, 'abc', False)

truncate

Syntax	truncate(number) : number
Arguments	value: The numeric value to be truncated.
Category	Numeric
Context	All Returns the given value truncated by dropping the fractional part.

Example Expression	Expected Result
truncate(1.9)	1
truncate(–1.9)	–1

tuple

Syntax	tuple(list<a>) : tuple<a, a>
Arguments	tupleVal: The values making up the tuple
Category	Tuple
Context	All Constructs a tuple from the provided values.

Example Expression	Expected Result
tuple(1, 'abc')	tuple(1, 'abc')
tuple(True, 999, 'abc')	tuple(True, 999, 'abc')

union

Syntax	union(list<a>,list<a>) : list<a>
Arguments	list1: The first list to union. list2: The second list to union.
Category	List
Context	All Makes a list that has the context of the two argument lists together. First list duplicates are preserved.

Example Expression	Expected Result
union({1,2,3},{4,5,6})	{1,2,3,4,5,6}
union({1,2,3,4},{3,4,5,6})	{1,2,3,4,5,6}
union({2},{2,2,2,2,2})	{2}
union({2,2,2,2,2}, {2})	{2,2,2,2,2}
union({}, {1,2,3})	{1,2,3}
union({4,5,6}, {})	{4,5,6}
union({}, {})	{}

unixTimeSecondsToDate

Syntax	unixTimeSecondsToDate(number) : time
Arguments	val: The Unix time in seconds to convert to a date.
Category	Conversion, Time
Context	All Converts a Unix timestamp to a date-time value.

Example Expression	Expected Result
year(unixTimeSecondsToDate(0))	1970
unixTimeSecondsToDate(1420070400)	2015-01-01 00:00:00 UTC

unlines

Syntax	unlines(list<text>) : text
Arguments	list: The list of the texts to join.
Category	Text
Context	All Joins a list of texts into a single text after appending a terminating newline to each. Any element of the list that is null or an empty text is skipped. Only one newline is appended between two non-empty texts.

Example Expression	Expected Result
unlines({'First fox', 'Second fox'})	"First fox nSecond fox"

unwords

Syntax	unwords(list<text>) : text
Arguments	list: Text items to be combined, separated with spaces
Category	Text
Context	All Joins a list of words with spaces.

Example Expression	Expected Result
unwords("The", "quick", "brown", "fox")	'The quick brown fox'
unwords({"The", "quick", "brown", "fox"})	'The quick brown fox'
unwords("Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo")	{'Buffalo','buffalo','Buffalo','buffalo','buffalo','buffalo','Buffalo','buffalo'}
unwords(words("Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo"))	'Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo'

upper

Syntax	upper(text) : text
Arguments	text: The texts to convert to upper case.
Category	Text
Context	All Returns the given text converted into upper case characters.

Example Expression	Expected Result
upper("The fox")	"THE FOX"

valueForAll

Syntax	valueForAll(a,b) : a valueForAll(a,b,c) : a valueForAll(a,b,c,d) : a
Arguments	val: The expression to evaluate in the modified context for all values of the specified key fields. keyField1: A field that defines the component of the evaluation context to evaluate over (dimension). keyField2: A second field that defines the component of the evaluation context to evaluate over (dimension). keyField3: A third field that defines the component of the evaluation context to evaluate over (dimension).
Category	Reference
Context	field, query Returns a data value relative to the current calculation by using all members of the given dimensions. This can be used to generate comparisons to specific values.

Example Expression	Expected Result
[Sales] / valueForAll([Sales], [Country])	0.11
[Sales] / valueForAll([Sales], [Country], [Product], [Time])	0.34

valueForOnly

Syntax	valueForOnly(a,b) : a valueForOnly(a,b,c) : a valueForOnly(a,b,c,d) : a
Arguments	val: The expression to evaluate in the modified context in the context of only the specified dimension keys. keyField1: A field that defines the component of the evaluation context to evaluate over (dimension). keyField2: A second field that defines the component of the evaluation context to evaluate over (dimension). keyField3: A third field that defines the component of the evaluation context to evaluate over (dimension).
Category	Reference
Context	field, query Returns a data value relative to the current calculation by using the current value of the given dimentions. This can be used to generate comparisons to specific values.

Example Expression	Expected Result
[Sales] / valueForOnly([Sales], [Country])	0.01
[Sales] / valueForOnly([Sales], [Country], [Product], [Time])	0.3

valueWithoutFilter

Syntax	valueWithoutFilter(a,b) : a valueWithoutFilter(a,b,c) : a valueWithoutFilter(a,b,c,d) : a
Arguments	val: The expression to evaluate in the modified context in the context of only the specified dimension keys. keyField1: A field that defines the component of the evaluation context to evaluate over (dimension). keyField2: A second field that defines the component of the evaluation context to evaluate over (dimension). keyField3: A third field that defines the component of the evaluation context to evaluate over (dimension).
Category	Reference
Context	field, query Returns the value of the first argument with filters suppressed on any of the subsequent argument fields. This can be used to reference values that would normally be outside the scope of data for the query.

Example Expression	Expected Result
[Sales] / valueWithoutFilter([Sales], [Country])	2.6
[Sales] / valueWithoutFilter([Sales], [Country], [Product], [Time])	5.35

weekday

Syntax	weekday(time) : number weekday(time,number) : number
Arguments	date: The date to use. option: The day numbering type.
Category	Time
Context	All Returns a number representing the day of the week for a given date. An optional second parameter controls the starting day of the week. The options for the second parameter are as follows: 1: Returns Sunday as 1 to Saturday as 7 (default). 2: Returns Monday as 1 to Sunday as 7. 3: Returns Monday as 0 to Sunday as 6.

Example Expression	Expected Result
weekday(toDate(2010, 10, 3))	1
weekday(toDate(2010, 10, 3), 1)	1
weekday(toDate(2010, 10, 9), 1)	7
weekday(toDate(2010, 10, 3), 2)	7
weekday(toDate(2010, 10, 5), 2)	5
weekday(toDate(2010, 10, 3), 3)	0

weightedAvgOfList

Syntax	weightedAvgOfList(list<number>) : number
Arguments	list: The list of values and weightings, where each value is followed by its weighting.
Category	List
Context	All Returns the weighted average of the non-null pairs of values and weights in a given list. Each value should be followed by the corresponding weighting. Values will be ignored where the value or the associated weighting is null. The list can be passed to the function as a literal list enclosed in braces or as a sequence of arguments.

Example Expression	Expected Result
weightedAvgOfList({1, 1, 2, 3, 7, 2})	3.50 = (11 + 23 + 7*2) / (1 + 3 + 2)
weightedAvgOfList(1, 200, null, 300)	1.00

white

Syntax

white() : color

Category

Color

Context

All

The color white.

words

Syntax	words(text) : list<text>
Arguments	text: The text to be split into words
Category	Text
Context	All Splits text separated by spaces into a list of words.

Example Expression	Expected Result
words("It has but established new")	{'It', 'has', 'but', 'established', 'new'}
words("a,b,c,d")	{'a,b,c,d'}
words("a, b, c, d")	{'a,', 'b,', 'c,', 'd'}
words("Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo")	{'Buffalo','buffalo','Buffalo','buffalo','buffalo','buffalo','Buffalo','buffalo'}
words(words("Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo"))	'Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo'

year

Syntax	year(time) : number
Arguments	time: The time from which to extract the year.
Category	Time
Context	All Returns the year of the given date-time value.

Example Expression	Expected Result
year([BonusDate])	2021

yellow

Syntax

yellow() : color

Category

Color

Context

All

The color yellow.