--- title: "Derived property formulas part 1: Operations and allowed values" slug: "derivation-formulas-part-1-operations-and-allowed-values" updated: 2026-08-27T12:39:27Z published: 2026-08-27T12:39:27Z canonical: "support.lusid.com/derivation-formulas-part-1-operations-and-allowed-values" --- > ## Documentation Index > Fetch the complete documentation index at: https://support.lusid.com/llms.txt > Use this file to discover all available pages before exploring further. # Derived property formulas part 1: Operations and allowed values A [derived property](/v1/docs/what-is-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. > [!WARNING] > **Required additional reading**: [Derived property formulas part 2: Entity components](/v1/docs/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](/v1/docs/derivation-formulas-part-2-entity-components) 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** ```plaintext ``` **Allowed values** `<operator>` can be `+`, `-`, `*`, `/` or `^` (to the power of). `<value>` can be: - A number - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) that is a number - The following nested operations that return a number: - Another **Mathematical** operation - [Numeric mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#numeric-mapping) - [Average](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#average-minimum-maximum-sum) - [Coalesce](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#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** ```plaintext # 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](https://en.wikipedia.org/wiki/ISO_8601#Durations) (you cannot add two dates). Note you can subtract one date from another to get a whole number of days more conveniently using [Tojuliandaynumber](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#tojuliandaynumber). 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](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#convert-to-iso8601-period), and *vice versa* using [Tostring](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#convert-to-string) or [Tonumber](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#convert-to-number). **Syntax** ```plaintext ``` **Allowed values** `<operator>` depends on `<value>` `<value>` can be: - A date or period - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) that is a date (there are no period entity components in LUSID) - Any operation that returns a date or period Note subtracting two dates where either is null returns `PT0S`. Adding or subtracting a period to/from a null date returns null. **Examples** | Operation | Example | Returns | Return type | | --- | --- | --- | --- | | Compare two dates | `2026-01-10T23:59:59Z gt 2026-01-01T00:00:00Z` | `True` | boolean | | Subtract two dates | `toString(2026-01-10T23:59:59Z - 2026-01-01T00:00:00Z)` | `P9DT23H59M59S` | period (must be converted to string or number) | | Add a period to a date | `2026-01-10T23:59:59Z + toPeriod('P6D')` | `2026-01-16T23:59:59Z` | date | | Subtract a period from a date | `2026-01-10T23:59:59Z - toPeriod(6) ` | `2026-01-04T23:59:59Z` | date | | Add two periods | `toString(toPeriod('P6D') + toPeriod(3))` | `P9D` | period (must be converted to string or number) | | Subtract two periods | `toString(toPeriod('P6D') - toPeriod(3))` | `P3D` | period (must be converted to string or number) | | Divide two periods | `toPeriod('P6D') / toPeriod(2)` | `3` | number | ## `Concat` **Syntax** ```plaintext Concat(, [, value…]) ``` **Allowed values** `<value>` can be (there must be at least two, each separated by a comma): - A number - A string - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - Any nested operation, including another `Concat` Note this operation concatenates all null values to null, so `Concat(null, null) = null`. Otherwise, nulls are skipped, so `Concat('a', null, 'b') = 'ab'`. **Examples** ```plaintext # 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** ```plaintext Map(: ) ``` **Allowed values** `<value>` can be: - A number - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - Another **Numeric mapping** operation - [Mathematical](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#mathematical-operations) - [Average](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#average-minimum-maximum-sum) - [Coalesce](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#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** ```plaintext 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** ```plaintext Map(: ) ``` Allowed values `<value>` can be: - A string - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - Another **String mapping** operation - [Concat](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#concatenation) - [Coalesce](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#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** ```plaintext 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`, `Min`, `Max`, `Sum` **Syntax** ```plaintext Average( [, value…]) Min( [, value…]) Max( [, value…]) Sum( [, 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](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - Another **Average**, **Minimum**, **Maximum** or **Sum** operation - [Mathematical](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#mathematical-operations) - [Numeric mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#numeric-mapping) - [Coalesce](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#coalesce) - [Absolute](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#absolute) Note these operations treat a null value as 0, so `Average(1, null, 5) = 2`. If an empty list, `Sum` returns 0 but the others return null. **Examples** These operations return a single number. ```plaintext 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](/v1/docs/how-do-i-create-a-compliance-rule). **Syntax** ```plaintext Count() ``` **Allowed values** `<value>` must be a list. The list may be: - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) that is a list - A nested operation that results in a list Note this operation counts a null value or a non-list value as 0, so for example `Count(null) = 0` **Examples** ```plaintext Count(ResourceLists[Compliance/ApprovedSectors]) ``` ## Absolute **Syntax** ```plaintext Abs( [, 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](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - [Mathematical](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#mathematical-operations) - [Numeric mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#numeric-mapping) - [Average, Minimum, Maximum, Sum](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#average-minimum-maximum-sum) Note this operation keeps nulls in a list as null, although a single null value is zero: `Abs(null) = 0`. **Examples** This operation returns a single number if just one is passed in. Otherwise, it returns a list of numbers. ```plaintext Abs(-5) Abs(1, -2, 3) ``` ## `Round`, `RoundUp`, `RoundDown` **Syntax** ```plaintext Round( [, ]) RoundUp( [, ]) RoundDown( [, ]) ``` The `Round` operation combines the functionality of the Excel [Round](https://support.microsoft.com/en-gb/office/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c) and [MRound](https://support.microsoft.com/en-gb/office/mround-function-c299c3b0-15a5-426d-aa4b-d2d5b3baf427) 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](https://support.microsoft.com/en-gb/office/ceiling-function-0a5cd7c8-0720-4f0a-bd2c-c943e510899f). If you do not, it behaves like Excel `RoundUp(<value>, 0)`. - If you provide a `<multiple>` for `RoundDown`, the operation behaves like Excel [Floor](https://support.microsoft.com/en-gb/office/floor-function-14bb497c-24f2-4e04-b327-b0b4de5a8886). If you do not, it behaves like Excel `RoundDown(<value>, 0)`. **Allowed values** `<value>` can be: - A number - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - [Mathematical](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#mathematical-operations) - [Numeric mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#numeric-mapping) - [Average, Minimum, Maximum, Sum](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#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 or a sign mismatch as 0, so `Round(null) = 0`. **Examples** | Round | RoundUp | RoundDown | | --- | --- | --- | | `Round(1234.918273645) = 1235` | `RoundUp(1234.918273645) = 1235` | `RoundDown(1234.918273645) = 1234` | | `Round(1234.918273645, 0.01) = 1234.92` | `RoundUp(1234.918273645, 0.01) = 1234.92` | `RoundDown(1234.918273645, 0.01) = 1234.91` | | `Round(1234.918273645, 0.1) = 1234.9` | `RoundUp(1234.918273645, 0.1) = 1235` | `RoundDown(1234.918273645, 0.1) = 1234.9` | | `Round(1234.918273645, 1) = 1235` | `RoundUp(1234.918273645, 1) = 1235` | `RoundDown(1234.918273645, 1) = 1234` | | `Round(1234.918273645, 2) = 1234` | `RoundUp(1234.918273645, 2) = 1236` | `RoundDown(1234.918273645, 2) = 1234` | | `Round(1234.918273645, 5) = 1235` | `RoundUp(1234.918273645, 5) = 1235` | `RoundDown(1234.918273645, 5) = 1230` | | `Round(1234.918273645, 10) = 1230` | `RoundUp(1234.918273645, 10) = 1240` | `RoundDown(1234.918273645, 10) = 1230` | | `Round(1234.918273645, 50) = 1250` | `RoundUp(1234.918273645, 50) = 1250` | `RoundDown(1234.918273645, 50) = 1200` | | `Round(1234.918273645, 100) = 1200` | `RoundUp(1234.918273645, 100) = 1300` | `RoundDown(1234.918273645, 100) = 1200` | ## `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](/v1/docs/what-is-a-data-type), and the expected number of decimal places by its `minorUnit` field. For example, calling the [ListDataTypes](https://www.lusid.com/docs/api/lusid/endpoints/data-types/ListDataTypes) API reveals the following specifications for `GBP`, `CLF` and `JPY`: | ```json { "code": "GBP", "displayName": "Pound Sterling", "description": "GREAT BRITAIN", "numericCode": 826, "minorUnit": 2, ... } ``` | ```json { "code": "CLF", "displayName": "Unidad de Fomento", "description": "CHILE", "numericCode": 990, "minorUnit": 4, ... } ``` | ```json { "code": "JPY", "displayName": "Yen", "description": "JAPAN", "numericCode": 392, "minorUnit": 0, ... } ``` | | --- | --- | --- | **Syntax** ```plaintext RoundIso4217Currency(, , [Up | Down]) ``` **Allowed values** `<value>` can be: - A number - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) that is a number - The following nested operations that return a number: - [Mathematical](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#mathematical-operations) - [Numeric mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#numeric-mapping) - [Average, Minimum, Maximum, Sum](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#average-minimum-maximum-sum) `<currency>` must match an [ISO 4217 currency code](https://en.wikipedia.org/wiki/ISO_4217#List_of_ISO_4217_currency_codes) and can be: - A string - An entity component that is a string - Any nested operation that returns a string This operation returns the value unchanged if the currency is null or not a ISO 4217 code, and returns null if the value isn’t a number. **Examples** | GBP | CLF | JPY | | --- | --- | --- | | `RoundIso4217Currency(1234.918273, 'GBP') = 1234.92` | `RoundIso4217Currency(1234.918273, 'CLF') = 1234.9183` | `RoundIso4217Currency(1234.918273, 'JPY') = 1235` | | `RoundIso4217Currency(1234.918273, 'GBP', Up) = 1234.92` | `RoundIso4217Currency(1234.918273, 'CLF', Up) = 1234.9183` | `RoundIso4217Currency(1234.918273, 'JPY', Up) = 1235` | | `RoundIso4217Currency(1234.918273, 'GBP', Down) = 1234.91` | `RoundIso4217Currency(1234.918273, 'CLF', Down) = 1234.9182` | `RoundIso4217Currency(1234.918273, 'JPY', Down) = 1234` | ## `Coalesce` **Syntax** ```plaintext Coalesce(, …) ``` **Allowed values** `<value>` can be (there must be at least two, each separated by a comma): - A number - A string - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - 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`. ```plaintext 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** ```plaintext Replace(: =) ``` **Allowed values** `<value>` can be: - A string - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - [Concat](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#concatenation) - [String mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#string-mapping) - [Coalesce](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#coalesce) Note this operation replaces a null value with null, so `Replace(null: 'instr'='instrument') = null`. **Examples** ```plaintext Replace(name: 'instr'='instrument') ``` ## `Lower`, `Upper`, `Proper`, `Sentence` **Syntax** ```plaintext Lower()   # Returns value in lowercase Upper()   # Returns value in uppercase Proper()   # Returns value with the first character of each word capitalised Sentence()   # Returns value with the first character of each sentence capitalised ``` **Allowed values** `<value>` can be: - A string - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - Another **Lower**, **Upper**, **Proper** or **Sentence** operation - [Concat](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#concatenation) - [String mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#string-mapping) - [Coalesce](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#coalesce) - [Substring](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#substring) - [Tostring](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#convert-to-string) **Examples** ```plaintext 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')       # Returns "The quick. Brown fox" ``` ## `Substring` **Syntax** ```plaintext Substring(, [, ]) ``` 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](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - [Concat](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#concatenation) - [String mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#string-mapping) - [Coalesce](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#coalesce) - [Lower, Upper, Proper, Sentence](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#lower-upper-proper-sentence) - [Tostring](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#convert-to-string) `<startIndex>` and `<length>` must be positive integers, and can be nested operations that return integers. This operation returns an empty string if `<startIndex>` is greater than the length of `<value>`. **Examples** ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image-K16SPZ8Z.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A00%3A51Z&se=2026-11-04T03%3A00%3A51Z&sr=c&sp=r&sig=LZawJOb9XxS1gRtZ4RDQFwtS0kB5aLooHeOFcsU%2BoZw%3D) ```plaintext # 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) ``` ## `Instring` **Syntax** ```plaintext Instring(, [, ]) ``` Like the Excel [FIND](https://support.microsoft.com/en-gb/office/find-function-c7912941-af2a-4bdf-a553-d0d89b0a0628) function, locates `<stringToFind>` within `<value>,` and returns the start position number of the first match from the first character of `<value>`. You can specify a `<startIndex>` to ignore the first *n* characters of `<value>` if you wish. Note the following: - The index is 1-based, so the starting position of `<value>` is 1, not 0. - The search is case-sensitive and accent-sensitive. - If `<startIndex>` is greater than the length of `<value>`, no match is found. - If no match is found, null is returned. **Allowed values** `<value>` and `<stringToFind>` can be: - A string - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - The following nested operations: - [Concat](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#concatenation) - [String mapping](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#string-mapping) - [Coalesce](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#coalesce) - [Lower, Upper, Proper, Sentence](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#lower-upper-proper-sentence) - [Tostring](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#convert-to-string) `<startIndex>` must be a positive integer, and can be a nested operation that returns an integer. **Examples** ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image-VOJMT326.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A00%3A51Z&se=2026-11-04T03%3A00%3A51Z&sr=c&sp=r&sig=LZawJOb9XxS1gRtZ4RDQFwtS0kB5aLooHeOFcsU%2BoZw%3D) ```plaintext # Returns 17 Instring('The quick brown fox jumped over the lazy dog', 'fox') # Returns 33 (the search starts at index 5, so the leading 'The ' is ignored) Instring('The quick brown fox jumped over the lazy dog', 'the', 5) # Returns null (no match is found) Instring('The quick brown fox jumped over the lazy dog', 'cat') ``` `Instring` can be nested inside [Substring](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#substring) to locate the start index or length dynamically: ```plaintext # Returns 'fox jumped' Substring('The quick brown fox jumped over the lazy dog', Instring('The quick brown fox jumped over the lazy dog', 'fox'), 10) # Returns 'The quick' Substring('The quick brown fox jumped over the lazy dog', 1, Instring('The quick brown fox jumped over the lazy dog', ' brown')) ``` ## `Tostring` **Syntax** ```plaintext Tostring() ``` **Allowed values** `<value>` can be: - A number, with other data types untested but likely to work - An [ISO8601 period](https://en.wikipedia.org/wiki/ISO_8601#Durations), for example `P5D` or `P23DT5H37M55S` - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - Any nested operation, including another **Convert to string** Note this operation converts a null value to an empty string. If a list, `Tostring` returns comma-separated elements. ## `Tonumber` **Syntax** ```plaintext Tonumber() ``` **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](https://en.wikipedia.org/wiki/ISO_8601#Durations), for example `P23DT5H37M55S` - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) that is a suitable string - Any nested operation that returns a suitable string or period Note the following: - A value that cannot be parsed returns 0, so `Tonumber('hello') = 0` - A null value returns 0, so `Tonumber(null) = 0` - Overflow returns null - `Tonumber(true) = 1` and `Tonumber(false) = 0` **Examples** ```plaintext Tonumber('1.25')      # Returns 1.25 Tonumber(P1DT6H)      # Returns 1.25 ``` ## `Toperiod` Returns an ISO8601 period, for use in [date operations](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#date-operations). **Syntax** ```plaintext Toperiod() ``` **Allowed values** `<value>` can be: - A string representing an [ISO8601 period](https://en.wikipedia.org/wiki/ISO_8601#Durations), for example `'P23DT5H37M55S'` - A number representing a number of days - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) 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** ```plaintext Toperiod('P5D')           # Returns P5D Toperiod(0.5)             # Returns PT12H Toperiod(23.23467234)     # Returns P23DT5H37M55S ``` ## `Tojuliandaynumber` Returns the [Julian day number](https://en.wikipedia.org/wiki/Julian_day) from a date, useful for returning a whole number of days between two dates. See also [date operations](/v1/docs/derivation-formulas-part-1-operations-and-allowed-values#date-operations). The time of day is discarded rather than contributing a fraction of a day, so all times on the same date give the same number. **Syntax** ```plaintext Tojuliandaynumber() ``` **Allowed values** `<value>` can be: - A date or datetime, for example `2026-08-26` or `2026-08-26T14:30:00Z` - A string of the form `YYYY-MM-DD` or `YYYYMMDD` - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) that is a suitable date or string - Any nested operation providing it returns a suitable date or string This operation returns null in the following circumstances: - A value that is not a date, so `Tojuliandaynumber('hello') = null` - A string date that is not a real calendar date, so `Tojuliandaynumber('2026-02-30') = null` - A null value, so `Tojuliandaynumber(null) = null` **Examples** ```plaintext # Returns 2440588 Tojuliandaynumber('1970-01-01') # Returns 2440588 Tojuliandaynumber('19700101') # Returns eg. 2461279 Tojuliandaynumber(Properties[Holding/default/TaxlotPurchaseDate]) # Returns the whole number of days between two dates eg. 2 Tojuliandaynumber(settlementDate) - Tojuliandaynumber(transactionDate) ``` ## Conditional statements **Syntax** ```plaintext If( ) Then Else ``` **Allowed values** `<value>` can be: - A number - A string - An [entity component](/v1/docs/derivation-formulas-part-2-entity-components) - Any nested operation, including another conditional statement `<operator>` can be any of these [filter operators](/v1/docs/filtering-information-retrieved-from-lusid#appendix-a-supported-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](/v1/docs/derivation-formulas-part-2-entity-components) - Any nested operation, including another **If** **Examples** ```plaintext 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] ```