> For the complete documentation index, see [llms.txt](https://docs.usepylon.com/pylon-docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.usepylon.com/pylon-docs/account-intelligence/fields/formulas.md).
# Formulas
Intelligently calculated account fields to help with decisioning on accounts
With formulas, you have access to an array of manipulations and calculations you can perform on your data to create additional fields.
Formulas can have any data type available in [Custom Fields](/pylon-docs/platform/custom-fields.md), and are simply a calculation that is performed to fill in that data field. A formula field is usable across the product anywhere custom fields are, including in views, triggers, the API, etc.
### Example: Number of Meetings
For example, a formula to calculate the number of meetings you've had with a customer in the last 90 days.
{% hint style="info" %}
For meeting records, `QUERYCOUNT` defaults to the previous 90 days when no time range is provided. It does not count all historical meetings. To use a different range, provide a condition (`null` when none), a time value, and a unit:
`QUERYCOUNT({{ google_calendar.calendar_meeting.calendar_meetings }}, null, 180, "d")`
Supported units are `"d"` for days, `"w"` for weeks, `"m"` for months, and `"y"` for years.
{% endhint %}
### Example: Usage Percentage
Here is an example of using division on two synced fields to calculate seat utilization percentage.
### Example: Channel Members Left
The number of customer users who have left your channel in the last 30 days.
### Example: Number of Feature Requests
The number of issues that are tied with engineering work (via. [Ticketing](/pylon-docs/integrations/ticketing.md) integrations).
### Example: Lifecycle Stage
The stage of the customer (onboarding or renewal) based on their progress through the contract.
Examples of other formulas are:
* Calculating the number of issues that were categorized as bugs in the last 30 days
* Calculating the number of [Custom Activities](/pylon-docs/account-intelligence/activities/custom-activities.md) of a certain type that have occurred
* Creating a weighted health score across different components
## Formula syntax
Use the variable picker to insert fields available to your formula. Text literals use double quotes; numbers, `true`, `false`, and `null` do not. Functions can be nested when the inner result has the type the outer function expects.
For example, `ROUND(AVERAGE(1, 2, 3), 0)` returns `2`; `IF(ISNULL(null), "Missing", "Present")` returns `"Missing"`.
Use `+`, `-`, `*`, and `/` for arithmetic, and `=`, `!=`, `<`, `<=`, `>`, and `>=` for comparisons. Use parentheses to group expressions. A division by zero is an error.
## Function reference
Square brackets mark optional arguments; `...` means additional arguments of the same kind. Do not type these notation characters into your formula. Arguments named `condition` are Boolean expressions. Choose fields and datasets from the variable picker rather than copying a field name from an unrelated workspace.
### Dates and times
| Function and arguments | Returns | Behavior |
| -------------------------------------- | --------------------- | ----------------------------------------------------------------------------------------------------------------- |
| `DATE(value) / DATE(year, month, day)` | Date | Converts date/time text or Unix milliseconds to a UTC date, or constructs a date from three numbers. |
| `DATETIME(value)` | Date and time | Converts date/time text or Unix milliseconds to a UTC timestamp. |
| `DATEADD(date, amount, unit)` | Date or date and time | Adds an interval; use a negative amount to subtract. Units: year, quarter, month, week, day (singular or plural). |
| `DATEDIF(start, end, unit)` | Number | Difference in days, months, or years. Units: D/DAYS, M/MONTHS, Y/YEARS. |
| `DAY(date)` | Number | Day of the month, from 1 to 31. |
| `MONTH(date)` | Number | Month, from 1 to 12. |
| `YEAR(date)` | Number | Year. |
| `NETWORKDAYS(start, end[, holidays])` | Number | Weekdays between the dates, excluding Saturdays, Sundays, and optional holiday dates. |
| `NOW()` | Date and time | Current timestamp. |
| `TODAY()` | Date | Current date at midnight. |
| `TEXT(date, format)` | Text | Formats a date using the reference layout 2006-01-02 15:04:05. For example, TEXT(TODAY(), "Jan 2, 2006"). |
For example, `DATEADD(DATE(2026, 1, 31), 1, "month")` returns February 28, 2026. Month and quarter additions stop at the last day of the target month when needed. `NETWORKDAYS(DATE(2026, 10, 5), DATE(2026, 10, 9))` returns `5`: both endpoints are included.
### Numbers
| Function and arguments | Returns | Behavior |
| ------------------------------------- | ------- | --------------------------------------------------------------------------------------- |
| `ABS(number)` | Number | Absolute value. |
| `AVERAGE(number, ...)` | Number | Arithmetic mean of one or more numbers. |
| `MAX(number, ...)` | Number | Largest of one or more numbers. |
| `MEDIAN(number, ...)` | Number | Middle value; averages the two middle values for an even count. |
| `MIN(number, ...)` | Number | Smallest of one or more numbers. |
| `MOD(number, divisor)` | Number | Remainder after division; divisor must not be zero. |
| `MODE(number, ...)` | Number | Most frequently occurring value. |
| `PERCENTILE(number, ..., percentile)` | Number | Percentile from 0 to 1. The final argument is the percentile: 0.95 means p95. |
| `POWER(base, exponent)` | Number | Raises the base to the exponent. |
| `ROUND(number[, digits])` | Number | Rounds to the nearest value; halfway values round away from zero. Digits defaults to 0. |
| `ROUNDDOWN(number[, digits])` | Number | Rounds toward zero; digits defaults to 0. |
| `ROUNDUP(number[, digits])` | Number | Rounds away from zero; digits defaults to 0. |
| `SQRT(number)` | Number | Square root; number must be nonnegative. |
| `STDEV(number, number, ...)` | Number | Sample standard deviation; requires at least two numbers. |
| `SUM(number, ...)` | Number | Total of one or more numbers. |
| `VAR(number, number, ...)` | Number | Sample variance; requires at least two numbers. |
For example, `ROUND(3.14159, 2)` returns `3.14`, and `PERCENTILE(10, 20, 30, 0.5)` returns `20`.
### Text and collections
| Function and arguments | Returns | Behavior |
| ------------------------------------------ | ------- | ------------------------------------------------------------------------------------------------------------------ |
| `CONCAT(text, ...)` | Text | Joins text values. |
| `CONTAINS(collection_field, item)` | Boolean | Checks membership in a collection field inserted with the variable picker. Comparisons are case-sensitive. |
| `FIND(substring, text)` | Number | Case-sensitive position, starting at 1. Errors if the substring is absent. |
| `LEFT(text, count)` | Text | First count characters; count must be nonnegative. Returns the full text if count exceeds its length. |
| `LEN(value)` | Number | Character count for text, item count for collections, 0 for null, and 1 for other non-null scalars. |
| `LOWER(text)` | Text | Converts to lowercase. |
| `MID(text, start, count)` | Text | Characters beginning at the 1-based start position. |
| `REPLACE(text, start, count, replacement)` | Text | Replaces count characters beginning at the 1-based start position. |
| `RIGHT(text, count)` | Text | Last count characters; count must be nonnegative. |
| `SEARCH(substring, text)` | Number | Case-insensitive position, starting at 1. Errors if the substring is absent. |
| `SPLIT(text, delimiter, index)` | Text | Element at the 0-based index after splitting. Index must be nonnegative; an out-of-range index returns empty text. |
| `TRIM(text)` | Text | Removes leading and trailing whitespace. |
| `UPPER(text)` | Text | Converts to uppercase. |
For example, `SPLIT("red,green,blue", ",", 1)` returns `"green"`, because indexes start at `0`. `SEARCH("SUPPORT", "Pylon support")` returns `7` and ignores case; `FIND` requires matching case.
### Conditions
| Function and arguments | Returns | Behavior |
| ---------------------------------------- | -------------------------- | ------------------------------------------------------------------------------------------------------- |
| `AND(condition, condition, ...)` | Boolean | True when every condition is true; requires at least two conditions. |
| `NOT(condition)` | Boolean | Inverts the condition. |
| `OR(condition, condition, ...)` | Boolean | True when any condition is true; requires at least two conditions. |
| `IF(condition, when_true[, when_false])` | Type of the selected value | Returns one branch. If the condition is false and the false branch is omitted, returns a missing value. |
| `ISNULL(value)` | Boolean | True for a null or missing value. Empty text is not null. |
| `MULTI(condition, value, ...)` | Collection | Returns all values whose conditions match. Supply condition/value pairs. |
| `SELECT(condition, value, ...)` | Text | Returns the first matching value as text, or empty text if none match. Supply condition/value pairs. |
For example, `SELECT(false, "Urgent", true, "Normal")` returns `"Normal"`. `MULTI(true, "Renewal", true, "Expansion")` returns both labels as a collection.
### Related-record queries
| Function and arguments | Returns | Behavior |
| --------------------------------------------------------------- | -------------- | ----------------------------------------------------------------------------------- |
| `QUERYCOUNT(dataset[, condition, amount, unit])` | Number | Counts matching related records. |
| `QUERYSUM(field[, condition, amount, unit])` | Number | Totals a numeric field across matching records. |
| `QUERYAVERAGE(field[, condition, amount, unit])` | Number | Averages a numeric field across matching records. |
| `QUERYMIN(field[, condition, amount, unit])` | Number or date | Smallest numeric value or earliest date. |
| `QUERYMAX(field[, condition, amount, unit])` | Number or date | Largest numeric value or latest date. |
| `QUERYPERCENTILE(field, percentile[, condition, amount, unit])` | Number | Dataset percentile from 0 to 100: 95 means p95. This differs from PERCENTILE. |
| `QUERYANY(dataset, condition[, amount, unit])` | Boolean | Whether any record matches the condition. Returns false when there are no records. |
| `QUERYALL(dataset, condition[, amount, unit])` | Boolean | Whether every record matches the condition. Returns true when there are no records. |
These functions query records related to the account. The default window is the previous 90 days, except for contact datasets, which include all linked contacts when no window is specified. Set `amount` and `unit` to change it; supported units are `"d"`, `"w"`, `"m"`, and `"y"`. Here, a month means 30 days and a year means 365 days. The date used to select records depends on the dataset, such as issue creation time or calendar meeting start time.
To specify a window without a condition, use `null` for the condition: `QUERYCOUNT({{ google_calendar.calendar_meeting.calendar_meetings }}, null, 180, "d")`. Unlike `SUM` or `AVERAGE`, query functions operate across records rather than only the values passed into one expression.
### Account field history
| Function and arguments | Returns | Behavior |
| ----------------------------------------- | ----------------- | ---------------------------------------------------------------- |
| `GETVALUE(field, date)` | Type of the field | Value at the end of the specified date, written as "mm/dd/yyyy". |
| `GETVALUERELATIVE(field, amount[, unit])` | Type of the field | Historical value relative to today; unit defaults to "d". |
| `QUERYCHANGE(field, amount, unit)` | Number | Current numeric value minus its historical value. |
| `QUERYCHANGEPCT(field, amount, unit)` | Number | Percentage change: (current − historical) / historical × 100. |
For example, `GETVALUERELATIVE({{ account.type }}, 30, "d")` reads the account type recorded 30 days ago. Choose the field from the variable picker; the result depends on that account's recorded history. `GETVALUE({{ account.type }}, "09/01/2026")` reads its value at the end of September 1, 2026, in UTC. Relative history windows use 30 days per month and 365 days per year, measured back from the current time.
Select an account field with recorded history. These functions read field changes rather than related issue or meeting records. Relative units include `"d"`/`"day"`, `"w"`/`"week"`, `"m"`/`"month"`, and `"y"`/`"year"`. `QUERYCHANGE` and `QUERYCHANGEPCT` require numeric historical values and return `0` if either value is missing. `QUERYCHANGEPCT` returns `0` when both values are zero; a zero historical value with a nonzero current value is a division-by-zero error. See [Field History](/pylon-docs/platform/custom-fields/field-history.md).
### Missing values and errors
Use `ISNULL` to distinguish a missing value from `0`, `false`, or empty text. Functions and operators have different missing-value behavior; do not assume every null becomes zero. A single missing argument generally produces a missing result, except for `ISNULL` and `LEN`. Errors such as invalid argument types, division by zero, a negative square root, or an unsuccessful `FIND`/`SEARCH` must be corrected in the expression.
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.
Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:
```
GET https://docs.usepylon.com/pylon-docs/account-intelligence/fields/formulas.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
