Derived property formulas part 1: Operations and allowed values

A derived property must have a derivation formula that instructs LUSID how to automatically calculate values from one or more data fields or properties, including other derived properties.

Required additional reading: Derived property formulas part 2: Entity components

A derivation formula consists of one or more operations on one or more values. Note the following:

• Operations can be nested. So for example a Mathematical operation can add a number to the result of another, nested Mathematical operation.

• Operations can be chained within the If operation using the AND/OR logical operators.

• The term entity component in the table below is used to refer to all the data fields, properties, identifiers and other data structures belonging to entities available to be operated on.

Mathematical operations

Syntax

<value> <operator> <value>

Allowed values

<operator> can be +, -, *, / or ^ (to the power of).

<value> can be:

• A number

• An entity component that is a number

• The following nested operations that return a number:

  • Another Mathematical operation

  • Numeric mapping

  • Average

  • Coalesce

Note the following:

• A null value (that is, a missing value) is treated as zero, so 5 * null = 0.

• Dividing by zero results in a zero value, so 5 / 0 = 0  and 5 / null = 0.

Examples

# Multiply outstanding shares by price:
Properties[Instrument/default/SharesOutstanding] * Properties[Instrument/default/Price]

# Divide cost plus price by outstanding shares:
(Properties[Instrument/default/Cost] + Properties[Instrument/default/Price]) / Properties[Instrument/default/SharesOutstanding]

# Square price minus cost:
(Properties[Instrument/default/Price] - Properties[Instrument/default/Cost]) ^ 2

Date operations

You can compare two dates using the gt, gte, lt and lte operators to return true or false.

You can subtract one date from another to return an ISO8601 period (you cannot add two dates).

You can add and subtract a period from a date or from another period to return a period.

You can divide two periods to return a number of days (you cannot multiply two periods).

Note: There is no period data type in LUSID, so you must convert strings or numbers to periods using toPeriod, and vice versa using toString or toNumber.

Syntax

<value> <operator> <value>

Allowed values

<operator> depends on <value>

<value> can be:

• A date or period

• An entity component that is a date (there are no period entity components in LUSID)

• Any operation that returns a date or period

Examples

Concatenation

Syntax

Concat(<value>, <value> [, value…])

Allowed values

<value> can be (there must be at least two, each separated by a comma):

• A number

• A string

• An entity component

• Any nested operation, including another Concatenation

Note this operation concatenates null values to null, so Concat(null, null) = null.

Examples

# Return 'Instrument name - Currency (Country)':
Concat(Properties[Instrument/default/Name], ' - ', Properties[Instrument/default/Currency], '(', Properties[Instrument/default/Country], ')')

# Return 'Name - LUID':
Concat(Name, ' - ', Luid)

Numeric mapping

Syntax

Map(<value>: <expression>)

Allowed values

<value> can be:

• A number

• An entity component

• The following nested operations:

  • Another Numeric mapping operation

  • Mathematical

  • Average

  • Coalesce

<expression> is a comma-separated list of one or more of:

• <string>=<number>

• <number>=<number>

• default=<number>

Note this operation maps a null value to null if no default is set, so:

• Map(null: 'AA'=1) = null

• Map(null: 'AA'=1, default=0) = 0

Examples

Map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3, default=0)

Map(Properties[Instrument/default/Duration]: '3 months'=3, '9 months'=9)

String mapping

Syntax

Map(<value>: <expression>)

Allowed values

<value> can be:

• A string

• An entity component

• The following nested operations:

  • Another String mapping operation

  • Concatenation

  • Coalesce

<expression> is a comma-separated list of one or more of:

• <string>=<string>

• <number>=<string>

• default=<string>

Note this operation maps a null value to null if no default is set, so:

• Map(null: '3 months'='3m') = null

• Map(null: '3 months'='3m', default='0m') = 0m

Examples

Map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m')

Map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m', default = '0m')

Average, Minimum, Maximum, Sum

Syntax

Average(<value> [, value…])

Min(<value> [, value…])

Max(<value> [, value…])

Sum(<value> [, value…])

Allowed values

<value> can be (there can be multiple separate values, or just one if it is a data field that is a list):

• A number

• An entity component

• The following nested operations:

  • Another Average, Minimum, Maximum or Sum operation

  • Mathematical

  • Numeric mapping

  • Coalesce

  • Absolute

Note these operations treat a null value as 0, so Average(1, null, 5) = 2.

Examples

These operations return a single number.

Average(Map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3), Map(Properties[Instrument/default/Moodys]: 'A'=1, 'B+'=2, 'C'=3)) 

Min(Map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3), Map(Properties[Instrument/default/Moodys]: 'A'=1, 'B+'=2, 'C'=3))

Max(Map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3), Map(Properties[Instrument/default/Moodys]: 'A'=1, 'B+'=2, 'C'=3))

Sum(Abs(1, -2, 3))

Count

Note: This operation can only be used in compliance rules.

Syntax

Count(<value>)

Allowed values

<value> must be a list. The list may be:

• An entity component that is a list

• A nested operation that results in a list

Note this operation counts a null value as null, so Count(null) = null.

Examples

Count(ResourceLists[Compliance/ApprovedSectors])

Absolute

Syntax

Abs(<value> [, value…])

Allowed values

<value> can be (there can be multiple separate values, or just one if it is a data field that is a list):

• A number

• An entity component

• The following nested operations:

  • Mathematical

  • Numeric mapping

  • Average, Minimum, Maximum, Sum

Note this operation treats a null value as 0, so Abs(null) = 0.

Examples

This operation returns a single number if just one is passed in. Otherwise, it returns a list of numbers.

Abs(-5)

Abs(1, -2, 3)

Round, RoundUp, RoundDown

Syntax

Round(<value> [, <multiple>])

RoundUp(<value> [, <multiple>])

RoundDown(<value> [, <multiple>])

The Round operation combines the functionality of the Excel Round and MRound functions. The <multiple> is optional.

If you provide a <multiple> for Round, the operation behaves like Excel MRound. If you do not, it behaves like Excel Round(<value>, 0).

• To emulate Excel Round with positive <num_digits> to round to a number of decimal places, specify a <multiple> that is a fraction of 1, so for example Round(567.1234, 0.01) = 567.12.

• To emulate Excel Round with negative <num_digits> to round to the left of the decimal point, specify a <multiple> that is a multiple of 1, so for example Round(567.1234, 100) = 600.

Note that specifying Round with a positive <value> and a negative <multiple> behaves like Excel MRound and returns null, so Round(567.1234, -2) = null.

The behavior of the RoundUp and RoundDown operations is similar:

• If you provide a <multiple> for RoundUp, the operation behaves like Excel Ceiling. If you do not, it behaves like Excel RoundUp(<value>, 0).

• If you provide a <multiple> for RoundDown, the operation behaves like Excel Floor. If you do not, it behaves like Excel RoundDown(<value>, 0).

Allowed values

<value> can be:

• A number

• An entity component

• The following nested operations:

  • Mathematical

  • Numeric mapping

  • Average, Minimum, Maximum, Sum

<multiple> is an optional number to round to, with the caveat that it must have the same sign as <value>.

Note these operations treat a null value as 0, so Round(null) = 0.

Examples

RoundIso4217Currency

The RoundIso4217Currency operation rounds a currency amount to the expected number of decimal places for that currency, for example 2dp for GBP, 4dp for CLF  or 0dp for JPY.

By default, amounts are rounded to the nearest number of decimal places. You can optionally specify Up or Down to change this behavior.

Currencies that can be rounded are controlled by the built-in system/iso4217CurrencyAndAmount data type, and the expected number of decimal places by its minorUnit field. For example, calling the ListDataTypes API reveals the following specifications for GBP, CLF and JPY:

{
  "code": "GBP",
  "displayName": "Pound Sterling",
  "description": "UNITED KINGDOM OF GREAT BRITAIN",
  "numericCode": 826,
  "minorUnit": 2,
  ... 
}

{
  "code": "CLF",
  "displayName": "Unidad de Fomento",
  "description": "CHILE",
  "numericCode": 990,
  "minorUnit": 4,
  ... 
}

{
  "code": "JPY",
  "displayName": "Yen",
  "description": "JAPAN",
  "numericCode": 392,
  "minorUnit": 0,
  ... 
}

Syntax

RoundIso4217Currency(<value>, <currency>, [Up | Down])

Allowed values

<value> can be:

• A number

• An entity component that is a number

• The following nested operations that return a number:

  • Mathematical

  • Numeric mapping

  • Average, Minimum, Maximum, Sum

<currency> must match an ISO 4217 currency code and can be:

• A string

• An entity component that is a string

• Any nested operation that returns a string

Examples

Coalesce

Syntax

Coalesce(<value>, <value>…)

Allowed values

<value> can be (there must be at least two, each separated by a comma):

• A number

• A string

• An entity component

• Any nested operation, including another Coalesce

Note this operation coalesces null values to null, so Coalesce(null, null) = null.

Examples

This operation gives preference to property values in the order they are specified. So in the first example below, the derived property takes its description value from Commbank if that property exists, from Monzo if not, and finally from AmericanExpress if neither. If none of these properties exist, the derived property has the value Unknown.

Coalesce(Properties[Transaction/Commbank/description], Properties[Transaction/Monzo/Description], Properties[Transaction/AmericanExpress/Description], 'Unknown')

Coalesce(Properties[Transaction/Commbank/Price], Properties[Transaction/Monzo/Price], Properties[Transaction/AmericanExpress/Price], -1)

Replace

Syntax

Replace(<value>: <string>=<string>)

Allowed values

<value> can be:

• A string

• An entity component

• The following nested operations:

  • Concatenation

  • String mapping

  • Coalesce

Note this operation replaces a null value with null, so Replace(null: 'instr'='instrument') = null.

Examples

Replace(name: 'instr'='instrument')

Lower, Upper, Proper, Sentence

Syntax

Lower(<value>)   # Returns value in lowercase

Upper(<value>)   # Returns value in uppercase

Proper(<value>)   # Returns value with the first character of each word capitalised

Sentence(<value>)   # Returns value with the first character of each sentence capitalised

Allowed values

<value> can be:

• A string

• An entity component

• The following nested operations:

  • Another Lower, Upper, Proper or Sentence operation

  • Concatenation

  • String mapping

  • Coalesce

  • Substring

  • Convert to string

Examples

# Returns "the quick brown fox"
Lower('The quick brown fox')

# Returns "THE QUICK BROWN FOX"
Upper('The quick brown fox')

# Returns "The Quick Brown Fox"
Proper('The quick brown fox')

# Returns "The quick. brown fox"
Sentence('The quick. Brown fox')

Substring

Syntax

Substring(<value>, <startIndex> [, <length>])

Returns a substring of <value>, from <startIndex> (where 1 is the first character) for <length> characters, or until the end if no <length> is specified.

Allowed values

<value> can be:

• A string

• An entity component

• The following nested operations:

  • Concatenation

  • String mapping

  • Coalesce

  • Lower, Upper, Proper, Sentence

  • Convert to string

<startIndex> and <length> must be positive integers. This operation returns an empty string if <startIndex> is greater than the length of <value>.

Examples

# Returns "The quick br"
Substring('The quick brown fox jumped over the lazy dog', 1, 12)

# Returns "ick"
Substring('The quick brown fox jumped over the lazy dog', 7, 3)

Convert to string

Syntax

Tostring(<value>)

Allowed values

<value> can be:

• A number, with other data types untested but likely to work

• An ISO8601 period, for example P5D or P23DT5H37M55S

• An entity component

• Any nested operation, including another Convert to string

Note this operation converts a null value to an empty string.

Convert to number

Syntax

Tonumber(<value>)

Allowed values

<value> can be:

• A string that can be parsed to a number, for example '1' or '1.123' (parsing other data types to numbers should be supported soon)

• An ISO8601 period, for example P23DT5H37M55S

• An entity component that is a suitable string

• Any nested operation that returns a suitable string or period

Note this operation converts the following to 0:

• A value that cannot be parsed, so Tonumber('hello') = 0

• A null value, so Tonumber(null) = 0

Examples

# Returns 1.25
Tonumber('1.25')

# Returns 1.25
Tonumber(P1DT6H)

Convert to ISO8601 period

Returns an ISO8601 period, for use in date operations.

Syntax

Toperiod(<value>)

Allowed values

<value> can be:

• A string representing an ISO8601 period, for example 'P23DT5H37M55S'

• A number representing a number of days

• An entity component that is a suitable string or number

• Any nested operation providing it returns a suitable string or number

Note this operation converts the following to PT0S:

• A value that cannot be parsed, so Toperiod('hello') = PT0S

• A null value, so Toperiod(null) = PT0S

Examples

# Returns P5D
Toperiod('P5D')

# Returns PT12H
Toperiod(0.5) 

# Returns P23DT5H37M55S
Toperiod(23.23467234)

If then else

Syntax

If(<value> <operator> <value>) 
Then <trueValue> 
Else <falseValue>

Allowed values

<value> can be:

• A number

• A string

• An entity component

• Any nested operation, including another If

<operator> can be any of these filter operators, for example:

• eq

• neq

• gt

• startswith

• …

<trueValue> and <falseValue> can be:

• The reserved words True and False respectively.

• A number

• A string

• An entity component

• Any nested operation, including another If

Examples

If(Properties[Transaction/Commbank/Price] gt Properties[Transaction/Commbank/Cost]) Then True Else False

If(Name eq 'InstrumentCommbank' and State eq 'Active') Then 'Commbank' Else Name

If(Properties[Instrument/Ibor/Country] neq 'UK' or Properties[Instrument/Ibor/Region] neq 'EMEA') Then 'Others' Else Properties[Instrument/Ibor/Country]