Views:

Related resources:

Explanation

Tutorials

How-to guides

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.

Note: A derivation formula is stored as part of a derived property type.

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.
    Required reading: Derivation formulas part 2: Entity components
OperationSyntaxAllowed values
Mathematical<value> <operator> <value>

<value> can be:

  • A number
  • An entity component
  • The following nested operations:
    • Mathematical
    • Numeric mapping
    • Average
    • Coalesce

<operator> can be:

  • +
  • -
  • *
  • /
  • ^ (to the power of)

Examples:

Properties[Instrument/default/SharesOutstanding] * Properties[Instrument/default/Price]
(Properties[Instrument/default/Cost] + Properties[Instrument/default/Price]) / Properties[Instrument/default/SharesOutstanding]
(Properties[Instrument/default/Price] - Properties[Instrument/default/Cost]) ^ 2

Concatenationconcat(<value>, <value>…)

<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
concat(Properties[Instrument/default/Name], ' - ', Properties[Instrument/default/Currency], '(', Properties[Instrument/default/Country], ')')
concat(Name, ' - ', Luid)
Numeric mappingmap(<value>: <expression>)

<value> can be:

  • A number
  • An entity component
  • The following nested operations:
    • Mathematical
    • Numeric mapping
    • Average
    • Coalesce

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

  • <string> = <number>
  • <number> = <number>
  • default = <number>
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 mappingmap(<value>: <expression>)

<value> can be:

  • A string
  • An entity component
  • The following nested operations:
    • Concatenation
    • String mapping
    • Coalesce

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

  • <string> = <string>
  • <number> = <string>
  • default = <string>
map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m')
map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m', Default = '0m')
Averageaverage(<value>, <value>…)

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

  • A number
  • An entity component
  • The following nested operations:
    • Mathematical
    • Numeric mapping
    • Average
    • Coalesce
average(Properties[Instrument/default/Price], Properties[Instrument/default/Cost])
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), map(Properties[Instrument/default/Fitch]: '1'=1, '2'=2, '3'=3))
Coalescecoalesce(<value>, <value>…)

<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 Coalesce prefers 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)

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

<value> can be:

  • A string
  • An entity component
  • The following nested operations:
    • Concatenation
    • String mapping
    • Coalesce
Replace(Name: 'instr'='instrument')
Ifif(<value> <operator> <value>) 
then <trueValue> 
else <falseValue>

<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:

  • A number
  • A string
  • An entity component
  • Any other nested operation, including another If
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 stringtoString(<value>)

<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
Convert to numbertoNumber(<value>)

<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

If <value> cannot be parsed (for example toNumber("hello")) the operation returns 0.

Keywords: derived property, derivation formula, mathematical operation