Derivation formulas part 1: Operations and allowed values

Updated
  • Updated on Feb 18, 2025
  • Published on Aug 19, 2024

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: Derivation 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 that are available to be operated on in the formula.

Mathematical operations

Syntax

<value> <operator> <value>

Allowed values

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

<value> can be:

  • A number

  • An entity component

  • The following nested operations:

    • Mathematical

    • Numeric mapping

    • Average

    • Coalesce

Note a null value (that is, a missing value) is treated as 0, so 5 * null = 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

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 other nested operation, including another Concatenation

Note this function concatenates null values to null, so concat(null, null) = null.

Examples

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

# Concatenate '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:

    • Mathematical

    • Numeric mapping

    • Average

    • Coalesce

Note this function maps a null value to null providing no default is set, so:

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

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

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

  • <string>=<number>

  • <number>=<number>

  • default=<number>

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:

    • Concatenation

    • String mapping

    • Coalesce

Note this function maps a null value to null providing no default is set, so:

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

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

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

  • <string>=<string>

  • <number>=<string>

  • default=<string>

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:

    • Mathematical

    • Numeric mapping

    • Coalesce

    • Abs

    • Another Average, Minimum, Maximum or Sum operation

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

Examples

Note 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

Syntax

count(<value>)

Allowed values

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

Note this function counts a null value as null, so count(null) = null.

Examples

count(ResourceLists[Compliance/ApprovedSectors])

Note

This operation can only be used in compliance rules.

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 or Sum

Note this function treats a null value as 0, so abs(null) = 0.

Examples

Note 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)

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 other nested operation, including another Coalesce

Note this function coalesces null values to null, so coalesce(null, null) = null.

Examples

Note 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 function replaces a null value with null, so replace(null: 'instr'='instrument') = null.

Examples

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

If

Syntax

if(<value> <operator> <value>) 
then <trueValue> 
else <falseValue>

Allowed values

<value> can be:

  • A number

  • A string

  • An entity component

  • Any other 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 other 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]

Convert to string

Syntax

toString(<value>)

Allowed values

<value> can be:

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

  • An entity component

  • Any other nested operation, including another Convert to string

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

Convert to number

Syntax

toNumber(<value>)

Allowed values

<value> can be:

  • A string representing a number, for example "1" or "1.123" (converting other data types to numbers should be supported soon)

  • An entity component that returns a number

  • Any other nested operation providing it returns a number

Note this function converts the following to 0:

  • A value that cannot be parsed, so toNumber("hello") = 0.

  • A null value, so toNumber(null) = 0.