---
title: Derived property functions and operators
description: "Reference for the string, math, object, date, array, and property functions and the conditional, set, and parallel operators available in derived property formulas."
product: data
lang: en
last_updated: 2026-11-20
token_estimate: 3552
---
# Derived property functions and operators

This reference lists every function and operator you can use in a [derived property](https://amplitude.com/docs/data/derived-properties) formula. For worked formulas that combine them, refer to [Derived property formula examples](https://amplitude.com/docs/data/derived-properties/formula-examples).

> For AI agents: a documentation index is available at [/docs/llms.txt](/docs/llms.txt). Append `.md` to any page URL for markdown, or send `Accept: text/markdown`.

## Plan availability

Derived Properties is available on the Growth and Enterprise plans. Go to [Amplitude pricing](https://amplitude.com/pricing) for plan details.

| Plan | Allowance | How to access |
| --- | --- | --- |
| Free | Not included | [Upgrade your plan](https://amplitude.com/pricing) |
| Plus | Not included | [Upgrade your plan](https://amplitude.com/pricing) |
| Growth | Included | Included in plan |
| Enterprise | Included | Included in plan |

_Availability reflects the latest Amplitude plans. If your organization is on an older plan, your access may differ. Check your plan under [Plans & Billing in your organization settings](/docs/admin/account-management/account-settings#organizational-settings), or contact your Amplitude account representative._

## String functions

- **`REGEXEXTRACT (text_property, regular_expression)`**

  - **Description**: Extracts substrings matching the regular expression.
  - **Example**: `REGEXEXTRACT("shirt-150", "[0-9]+")`
  - **Result**: `150`
- **`REGEXREPLACE (text_property, regular_expression, replacement_text)`**

  - **Description**: Replaces the property's values with text matching the regular expression with replacement text.
  - **Example**: `REGEXREPLACE("en-US", "-.*", "")`
  - **Result**: `en`
- **`CONCAT(property1, property2)`**

  - **Description**: Concatenates a property with another property or text value.
  - **Example**: `CONCAT("firstName", "lastName")`
  - **Result**: `firstName lastName`
- **`LOWERCASE (text_property)`**

  - **Description**: Lower cases all characters in property's values.
  - **Example**: `LOWERCASE("John")`
  - **Result**: `john`
- **`UPPERCASE (text_property)`**

  - **Description**: Upper cases all characters in property's values.
  - **Example**: `UPPERCASE("John")`
  - **Result**: `JOHN`
- **`SPLIT (property, separator, [index])`**

  - **Description**: Split a property based on a delimiter and return an array of split elements. Takes an optional index that returns the element at that index.
  - **Example**:

    - `SPLIT("a_b_c", "_")`
    - `SPLIT("john@example.com", "@", 0)`
  - **Result**:

    - `["a", "b", "c"]`
    - `"john"`
- **`REMOVE (property, text)`**

  - **Description**: Remove all occurrence of text in property.
  - **Example**: `REMOVE("en-US", "en-")`
  - **Result**: `US`
- **`EXTRACT_FROM_DICT (property, text)`**

  - **Description**: Extract a value from a dictionary string based on a specific key.
  - **Example**: `EXTRACT_FROM_DICT("{'id': 1, 'name': 'John', 'country': 'US'}", "name")`
  - **Result**: `John`

## Math functions

| Function | Description | Example | Result |
| --- | --- | --- | --- |
| `SUM(num_property1, num_property2)` | Adds a property with other properties or with numbers. Equivalent to the `+` operator. Use `ADD` as an alias. | `SUM(subtotal, tip) >>>  SUM(10, 2)` | 12 |
| `MINUS(num_property1, num_property2)` | Subtracts a property with other properties or with numbers. Equivalent to the `-` operator. Use `SUBTRACT` as an alias. | `MINUS(total, tip) >>> MINUS(12, 2)` | 10 |
| `MULTIPLY (num_property1, num_property2)` | Multiplies a property with other properties and/or with numbers. Equivalent to the `*` operator. | `MULTIPLY(price, quantity) >>> MULTIPLY(2.50, 4)` | 10 |
| `DIVIDE(numerator, denominator)` | Divides a property by another property or number. Equivalent to the `/` operator. | `DIVIDE(calorie_intake, calorie_goal) >>> DIVID(1000, 2000)` | 0.5 |
| `POWER(num_property, exponent)` | Takes the property's values to the exponent power. | `POWER(property, 3) >>> POWER(2, 3)` | 8 |
| `MIN(num_property1, num_property_2)` | Returns the minimum value between two numbers. | `MIN(5, 10)` | 5 |
| `MAX(num_property1, num_property_2)` | Returns the maximum value between two numbers. | `MAX(5, 10)` | 10 |
| `CEIL(num_property)` | Rounds up to the nearest integer. | `CEIL(3.8)` | 4.0 |
| `FLOOR(num_property)` | Rounds down to the nearest integer. | `FLOOR(3.8)` | 3.0 |

## Object functions

| Function | Description | Example | Result |
| --- | --- | --- | --- |
| `EXTRACT_FROM_DICT (property, text)` | Extract a value from a dictionary string based on a specific key. | `EXTRACT_FROM_DICT("{'id': 1, 'name': 'John', 'country': 'US'}", "name")` | `John` |

## Date/ time functions

Amplitude requires all Unix timestamps to be in milliseconds.

| Function | Description | Example | Result |
| --- | --- | --- | --- |
| `DATE_TO_LONG (date_property)` | Convert date into Unix timestamp | `DATE_TO_LONG("2020-12-01")` | 1606780800000 |
| `TIME_TO_LONG (time_property)` | Convert date time (YYYY-MM-dd\[T\]HH:mm:ss) into Unix timestamp | `TIME_TO_LONG("2020-12-01 12:00:00")` | 1606780800000 |
| `LONG_TO_TIME (number_property)` | Convert Unix timestamp into date-time (local to user's timezone) | `LONG_TO_TIME (1606780800000)` | 2020-12-01 12:00:00 |
| `LONG_TO_DATE (number_property)` | Convert Unix timestamp into date (local to user's timezone) | `LONG_TO_DATE (1606780800000)` | 2020-12-01 |
| `DATE_TIME_FORMATTER (datetime_property, old_format, new_format)` | Convert format of a datetime property to a new format. Refer to [Java SimpleDateFormat](https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html) for more details. | `DATE_TIME_FORMATTER ("05.01.2021 12:00:00:000", "MM.dd.yyyy hh:mm:ss:SSS", "yyyy/MM/dd")` | 2021/05/01 |
| `TODAY()` | Current day represented as a long in epoch time in UTC. | `TODAY() - start_date_in_ms >>> 1609459200000 - 1577836800000` | 31622400000 |
| `EVENT_HOUR_OF_DAY()` | Get hour of day (0-23) from the event's timestamp. | `EVENT_HOUR_OF_DAY()` | 10 |
| `EVENT_DAY_OF_WEEK()` | Get day of week from the event's timestamp as string. For example, Monday. | `EVENT_DAY_OF_WEEK()` | Monday |

## Array functions

When performing computations on derived properties created from array properties, Amplitude assumes that only the first child is an array property. Amplitude only considers the first value of the other children, even when those other children are also array properties. Here are some illustrative examples:

```plaintext
Example 1  
 propA = [1,2,3], propB = [a,b,c]  
 CONCAT(propA, propB) = [1a, 2a, 3a]  
Example 2  
 propA = [1, 2, 3], propB = [a]  
 CONCAT(propA, propB) = [1a, 2a, 3a]  
Example 3  
 propA = [1], propB = [a, b, c]  
 CONCAT(propA, propB) = [1a]
```

| Function | Description | Example | Result |
| --- | --- | --- | --- |
| `ITEM_COUNT (property)` | Length of array property; defaults to 1 for non-arrayed properties. | `ITEM_COUNT(products)` | 3 |
| `GREATEST(property)` | Get max value of the array. | `GREATEST(prices)` | 10 |
| `LEAST(property)` | Get min value of the array. | `LEAST(prices)` | 2 |
| `COALESCE(property)` | Get the first non-null value of the array | `COALESCE(locations)` | 'California' |

## Property functions

When you select a property from the _Insert Property_ dropdown, Amplitude inserts a property function referencing that property directly into the editor. You can also manually insert this function wherever you want to reference a different Amplitude property.

These functions are available inside other functions.

| Function | Description | Example | Result |
| --- | --- | --- | --- |
| `PROPERTY(property_name, property_type)` | Reference to property within Amplitude. Possible property types: "user", "event", "derived", "lookup", "group" | `PROPERTY("first name","user")` | A reference to the user property "first name" in your project. This is how derived properties communicate with Amplitude’s query service. |
| `PROPERTY(property_name, "group", group_type)` | Reference to group property within Amplitude. Group type is required for group properties. | `PROPERTY("name","group", "business")` | A reference to the group property "name" in the group type "business" within your project. This is how derived properties communicate with Amplitude’s query service. |

### Reference Event Time

To use an event's timestamp in a derived property formula, select **Event Time** from the _Insert Property_ dropdown in the derived property builder.

Inside a derived property formula, `Event Time` is a long value in epoch milliseconds. To display it as a readable date, wrap it in `LONG_TO_TIME` or `LONG_TO_DATE`. Don't wrap `Event Time` in `TIME_TO_LONG` or `DATE_TO_LONG`, because the value is already a long and the conversion returns `none`. To compare `Event Time` against a date or datetime property, convert the other property to a long with `TIME_TO_LONG` or `DATE_TO_LONG` instead.

## Conditional operators

| Operator | Description | Example |
| --- | --- | --- |
| `IF(logical_expression, value_if_true, value_if_false)` | Returns `value_if_true` if `logical_expression` is true, otherwise returns `value_if_false`. | `IF(property == "(none)", "Property wasn't set", "Property was set")` |
| `AND(logical_expression_1, logical_expression_2)` | Returns True if both logical expressions are true, false otherwise. | `AND(is_subscribed == "true", has_valid_promo == "true")` |
| `OR(logical_expression_1, logical_expression_2)` | Returns True if any logical expression is true, false otherwise. | `OR(has_email == "true", has_phone == "true")` |
| `SWITCH(expression, case_1, value_1, [case_2, value_2 ...], [default])` | Evaluates an expression and returns values based on defined cases. Returns a default value if no cases are met if defined, otherwise null. | `SWITCH(tier, "gold", 2, "silver", 2, "bronze", 1, 0)` |

## String/numerical operators

| Operator | Example |
| --- | --- |
| `==` | `action == "purchase" ` |
| `!=` | `item_count != 0 ` |
| `contains` | `email contains "@gmail.com"` |
| `does not contain` | `title does not contain "officer" ` |
| `<`, `<=`, `>`, `>=` | `duration >= 60` |
| `glob match` | `url glob match "https://www.google.*/*"` |
| `glob does not match` | `query glob does not match "*/query=*"` |
| `has prefix` | `title has prefix "sir" ` |
| `does not have prefix` | `title does not have prefix "sir"` |

## Set operators

Set literals ("apple", "orange") must appear on the right hand side of the operator.

- **`==`**

  - **Example**:

    - `IF(product == ("apple","orange"), "true", "false")`
    - product = "apple", Returns "true"
- **`!=`**

  - **Example**:

    - `IF(product != ("apple","orange"), "true", "false")`
    - product = "banana", Returns "true"

## Parallel operators

Perform operations on arrays of data to help perform cart analysis.

| Operator | Description |
| --- | --- |
| `PARALLEL_SUM` | Adds corresponding values from two arrays at each position. |
| `PARALLEL_PRODUCT` | Multiplies corresponding values from two arrays at each position. Use this operator to calculate revenue for each item in a cart. For example, `PARALLEL_PRODUCT(PROPERTY('Products.price', 'event'), PROPERTY('Products.quantity','event'))` |
| `PARALLEL_MAX` | Returns the larger of the corresponding values from two arrays at each position. |
| `PARALLEL_MIN` | Returns the smaller of the corresponding values from two arrays at each position. |
| `SUM_ARRAY` | Sums all numeric elements in a single array property. Use this operator to calculate total cart value for filtering or grouping. For example, `SUM_ARRAY([1, 2, 4])` returns `7`. |

Parallel operators require at least one property to be a child cart property, and both properties must be under the same parent property.

For example, `products.price` and `products.quantity` are compatible. `products.price` and `shoppinglist.quantity` aren't compatible because they have different parent properties.

When you use a parallel operator with two arrays, it adds, multiplies, or compares corresponding values at each position. For example, `PARALLEL_SUM([1, 3, 5], [2, 4, 6])` returns `[3, 7, 11]`.

When one input is an array and the other is a scalar (single number), the scalar broadcasts to match the array's length. For example, `PARALLEL_SUM([1, 3, 5], 1)` returns `[2, 4, 6]`, and `PARALLEL_PRODUCT(2, [4, 5, 6])` returns `[8, 10, 12]`.

The derived property that a parallel operator creates becomes a child property of the parent cart property used in the formula. For example, creating a `revenue` derived property with `PARALLEL_PRODUCT(PROPERTY('products.price', 'event'), PROPERTY('products.quantity', 'event'))` makes `revenue` a child property of `products`.

> **Note:**
>
> To use a derived property created with a parallel operator in a chart, first select the parent cart property (marked with `{:}`). The derived property then appears in the child property selection list.

### Parallel operator example

You have a purchase event with the following cart property:

```json
"Products": [{
    "brand": "Apple","categories": "Digital Content","department": "Electronics",
    "price": 24.99,"quantity": 1
}, {
    "brand": "Adidas","categories": "Newsletter","department": "Electronics",
    "price": 24.99,"quantity": 1
}, {
    "brand": "Fossil","categories": "Digital Content","department": "Women's Clothing",
    "price": 24.99,"quantity": 2
}]
```

| Brand | Price | Quantity |
| --- | --- | --- |
| Apple | 24.99 | 1 |
| Adidas | 24.99 | 1 |
| Fossil | 24.99 | 2 |

Use `PARALLEL_PRODUCT` to create a `Revenue` derived property:

```plaintext
PARALLEL_PRODUCT(
    PROPERTY('Products.price', 'event'), 
    PROPERTY('Products.quantity','event')
)
```

If you add this property to a chart, group by `Brand` to view revenue by brand.

You can also use `SUM_ARRAY` on the `Revenue` derived property to calculate total cart value:

```plaintext
SUM_ARRAY(PROPERTY('Revenue', 'derived'))
```

This returns the sum of all revenue values across items in the cart, which is useful for filtering or grouping by total cart value.

