#### This article will help you:

- Understand and use custom formulas in Amplitude to create exactly the analysis you need

In an Event Segmentation or Data Table chart, the *Formula* option in the Measured As module's *Advanced* drop down offers you greater flexibility when performing analyses. Custom formulas are also useful for comparing various analyses on the same chart.

Choose from more than 20 custom formulas to plot the metrics you need. You can plot up to six formulas on the same chart, separated by semicolons.

This article will describe the mechanics of custom formulas, with examples of formulas you can use right now.

**NOTE:** While the Experiment Results chart also uses formula metrics, it does so in a different way than either the Event Segmentation or Data Table charts. To learn more about those differences, see this Help Center article on using formula metrics in Amplitude's Experiment Results chart.

### Feature availability

This feature is available to users on **Plus**, **Growth**, and **Enterprise** **plans** only. See our pricing page for more details.

## Formula syntax

In your formulas, refer to events selected in the Events Module by their corresponding letter. The functions and the parameters are not case sensitive. You can also perform the following arithmetic operations:

- Parenthesis ()
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)

For example, the letter A in the formula `UNIQUES(A)`

refers to the event `View Item Details`

while the letter B in the formula `UNIQUES(B)`

refers to the event `Add Item to Cart`

. This setup will display the ratio of users who viewed an item's details to users who placed an item in their cart.

You can also write a formula that consists of events, grouping each event by a property or properties. However, for the formula to be valid, the properties must have matching values across all events you are segmenting.

For example, if you have an event called `Page Name`

, the following property values would **not** match:

`Tutorial`

and`TUTORIAL`

(the matching is case sensitive)`1`

and`1.0`

(non-matching characters)

The order in which you are grouping properties by matters as well. Both events must have the *grouped by* values in the same order; otherwise, you will see a warning that events have no matching group by values.

You can also use custom formulas to uncover how many more times users in one cohort trigger a particular event than do users in another cohort.

To compare a metric between two different cohorts or user segments, add the number of the segment to the letter designating the event: `UNIQUES(A1)/UNIQUES(A2)`

. This will display a ratio of the performance of your cohorts on the same event as a single plotted line on your graph.

You can also view your metrics in percentages or dollars by adding the following prefixes to your formula:

- Percentage (%:)
- Dollars ($:)

## List of available formulas

This section lists available formulas by type: Metric, Aggregation, and Function. Click on a formula name to review its syntax.

### Metrics Formulas:

With metrics formulas, you can query on a metric for a particular event you are interested in. These formulas will be color-coded in green. Each metrics formula requires a letter corresponding to the event you're interested in as a parameter.

ACTIVE | ARPAU | AVG | FREQPERCENTILE |

HIST | PERCENTILE | PROPAVG | PROPCOUNT |

PROPCOUNTAVG | PROPHIST | PROPMAX | PROPMIN |

PROPSUM | REVENUETOTAL | TOTALS | UNIQUES |

EVENTTOTALS | SESSIONTOTALS |

### Aggregation Formulas:

Aggregation formulas let you query on a **rolling average or rolling window** for the metric and event you are interested in. These formulas will be color-coded in purple. Each aggregation formula requires **three** components: the metric you are aggregating, the event you are interested in, and the interval to aggregate by.

CUMSUM | ROLLAVG | ROLLWIN | ROLLWINBEFORE |

### Function Formulas:

Function formulas let you query on a mathematical function for a particular event and metric you're interested in. These formulas will be color coded in blue. Each function formula requires a value that can be either a constant, or another formula containing an event.

EXP | LN | LOG | LOG10 |

POWER | SQRT | TRENDLINE |

## Metrics formulas

### ACTIVE

**Syntax:** ACTIVE(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.

The `ACTIVE`

formula returns the percent of active users who triggered the event. This is the same as the `Active %`

metric in the Measured card, but here it is displayed in decimal fraction form. This setup will display the percentage of active users who have triggered the `View Item Details`

event.

### ARPAU

**Syntax: **$:ARPAU(event)

**Event:**Refers to the revenue event. This must be a letter that corresponds to an event in the Events card.- This function will only work if you are grouping by a numerical property on the event.

Returns the aggregate sum of the revenue event property formatted as a currency, divided by the number of unique active users in that same time period. It is equivalent to `PROPSUM(event) / UNIQUES(any active event)`

.

For example, the following setup shows the average revenue per active user of a generic e-commerce company:

As you can see in the screenshot above, the `$:`

prefix is optional. Its presence simply ensures the output format will be as a currency.

**NOTE**: ARPAU cannot be used in conjunction with aggregation formulas.

### AVG

**Syntax:** AVG(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.

Returns the average number of times the event was triggered. This function is equivalent to `TOTALS(event)/UNIQUES(event)`

. This setup will display the ratio of number of times `View Item Details`

was triggered to the number of times `Add Item to Cart`

was triggered, the average number of times ```
View
Item Details
```

was triggered, as well as the average number of times ```
Add Item
to Cart
```

was triggered on the same chart.

### FREQPERCENTILE

**Syntax: **FREQPERCENTILE(event, percentage)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.**Percentage:**Refers to the percentile you are interested in. This must be a value that is less than or equal to 1.

Returns the inputted percentile event frequency across all users. A percentile is a measure that indicates the value below which a given percentage of values fall. For example, the following formula shows the 90th percentile of users who triggered the `View Item Details`

event.

You can then take this information and create a behavioral cohort of your power users and further analyze them to see what distinguishes them from users who are not in the cohort.

### HIST

**Syntax: **HIST(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.

Returns the distribution of the event frequency per unique user over the selected time period. The following setup displays the distribution of event frequency for the `Complete Purchase`

event.

We can see that in the last 30 days, 22,075 users completed purchases five times.

The syntax for HIST varies slightly for the User Sessions chart as sessions are the focus of the metrics.

**Syntax**: HIST(session)

**Session:**Refers to the session you are interested in. This must be a letter that corresponds to a session in the Session card.

Returns the distribution of session durations (in seconds) over the selected time period. The following setup displays the distribution of the durations of all sessions.

### PERCENTILE

**Syntax: **PERCENTILE(event, percentage)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.**Percentage:**Refers to the percentile you are interested in. This must be a value that is less than or equal to 1.

**NOTE:** This function will only work if you are grouping by a numerical property on the event.

Returns inputted percentile of the property being grouped by. For example, the following formula will return the 90th percentile for revenue of all `Complete Purchase`

events.

Another example where the PERCENTILE formula can be useful is if you're tracking load times for your product, trying to ensure that a particular percentage of load times is below a certain threshold.

### PROPAVG

**Syntax: **PROPAVG(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.- This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula will perform the calculation with the first group-by clause.

Returns the average of the property values you are grouping by. This function is equivalent to `PROPSUM(event)/TOTALS(event)`

.

**NOTE**: The PROPAVG formula ignores events where the selected property value is `(none)`

.

The following setup will show the average of the revenue generated by completed purchases on a given day.

### PROPCOUNT

**Syntax: **PROPCOUNT(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card. If grouping by multiple properties, the formula will perform the calculation with the first group by clause.

Returns the number of distinct property values for the property the event is grouped by. In this setup, the formula will retrieve the number of different departments covering all the items for which details were viewed:

Note that `PROPCOUNT`

is an **estimate** of distinct property values. This estimate is generated by a HyperLogLog algorithm, and its accuracy depends on amount of data it has to work with. Expect a relative error in the range of 0.1% for less than 12,000 unique values, and up to 0.5% for more than 12,000 unique property values, depending on the cardinality of the property.

### PROPCOUNTAVG

**Syntax: **PROPCOUNTAVG(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card. If grouping by multiple properties, the formula will perform the calculation with the first group-by clause.

Returns the average number of distinct values each user has for a specified property.

For example, imagine you're interested in the average number of song genres your music app subscribers listen to. Every time a song is played, a ```
Play Song or
Video
```

event will trigger; each played song also captures a `Genre_Type`

event property. Running `PROPCOUNTAVG`

on `Play Song or Video`

grouped by `Genre_Type`

will give you the average number of unique `Genre_Type`

values users who fire `PlaySong or Video`

have.

### PROPHIST

**Syntax: **PROPHIST(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.- This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula will perform the calculation with the first group by clause.

Returns the distribution of the property values you are grouping by over the selected time period. The following setup will display the distribution of revenue over the last 30 days.

### PROPMAX

**Syntax**: PROPMAX(event)

**Event:**returns the maximum value of the property you are grouping the specified event by. The property must be numeric. If grouping by multiple properties, the calculation will be performed using the first group-by clause.

### PROPMIN

**Syntax**: PROPMIN(event)

**Event:**returns the minimum value of the property you are grouping the specified event by. The property must be numeric. If grouping by multiple properties, the calculation will be performed using the first group-by clause.

### PROPSUM

**Syntax: **PROPSUM(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card. The event must be grouped by the property you'd like to sum.- This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula will perform the calculation with the first group-by clause.

Returns the sum of the property values you are grouping the specified event by. For example, this visualization shows the total revenue generated by the `Complete Purchase`

event.

The syntax for PROPSUM varies slightly for the User Sessions chart as sessions are the focus of the metrics.

**Syntax**: PROPSUM(session)

**Session:**Refers to the session you are interested in. This must be a letter that corresponds to a session in the Session card.

Returns the total time (sum of the duration in seconds) of the specified session. For example, the following chart shows the total time (in seconds) summed across all sessions.

### REVENUETOTAL

**Syntax: **$:REVENUETOTAL(event)

**Event:**Refers to the revenue event. This must be a letter that corresponds to an event in the Event card.- This function will only work if you are grouping by a numerical property on the event. Also,

Returns the aggregate sum of the property, formatted as a currency. It is equivalent to `PROPSUM(event)`

. For example, the following setup shows the total revenue by day generated by purchases:

As you can see in the screenshot above, the `$:`

prefix is optional. Its presence simply ensures the output format will be as a currency.

### TOTALS

**Syntax: **TOTALS(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.

Returns the total number of times the event was triggered. This setup will show the total number of times an item's details were viewed, plus the total number of times an item was added to a cart.

### UNIQUES

**Syntax: **UNIQUES(event)

**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.

Returns the number of unique users who triggered the event. For example, the following setup shows the ratio of users who viewed an item's details to the users who added an item to their cart.

The syntax for UNIQUES varies slightly for the User Sessions chart as sessions are the focus of the metrics.

**Syntax**: UNIQUES(session)

**Session:**Refers to the session you are interested in. This must be a letter that corresponds to a session in the Session card.

Returns the number of unique users who engaged in session(s) defined by the specified session. For example, the following setup shows the ratio of users who engaged in sessions longer than one minute to the users who engaged in sessions that contained at least one `Search Items`

event.

### EVENTTOTALS

**Syntax: **EVENTTOTALS(session)

**Session:**Refers to the session you are interested in. This must be a letter that corresponds to a session in the Sessions card.

This formula metric is **only** available in the User Sessions chart. Returns the total number of events that were triggered during each session.

For example, the following setup shows the number of `Page Viewed`

events across all sessions.

### SESSIONTOTALS

**Syntax: **SESSIONTOTALS(session)

**Session:**Refers to the session you are interested in. This must be a letter that corresponds to a session in the Sessions card.

This formula metric is **only** available in the User Sessions chart. It returns the number of sessions defined by the specified session.

For example, the following setup shows the total number of sessions by day over the last 30 days for all users in the United Kingdom who completed at least one `Add to Cart`

event during each session.

## Aggregation formulas

### CUMSUM

**Syntax: **CUMSUM(metric, event)

**Metric:**The metric you wish to aggregate. This will be one of the metrics formulas listed above.

Returns the metric for selected event with a running total of days/weeks/months over the chart's timeframe.

For example, this chart shows a daily cumulative sum of revenue from `Complete Purchase`

events in the last 30 days. The data point for February 22nd will be a sum of revenue generated on February 20th, 21st, and 22nd.

For `CUMSUM(UNIQUES,A)`

, a deduplicated count of unique users will be returned for each data point.

### ROLLAVG

**Syntax: **ROLLAVG(metric, event, # of intervals)

**Metric:**The metric you wish to aggregate. This will be one of the metrics formulas listed above.**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.**Number of intervals:**The number of five-minute intervals, hours, days, weeks, or months to include in the rolling average. For example, a daily chart allows rolling averages over daily intervals only. The maximum ranges for a rolling average are 36 five-minute intervals (this works out to three hours), 72 hours, 90 days, 12 weeks, or 12 months.

Returns the metric for the event selected with a rolling average over the interval selected. For example, the following chart shows you your weekly rolling average superimposed on top of your daily active users.

The blue line in this chart shows daily active users and the green line shows the weekly rolling average. This is useful to see if your daily active user count is higher or lower than the rolling average.

### ROLLWIN

**Syntax: **ROLLWIN(metric, event, # of five-minute intervals/hours/days/weeks/months)

**Metric:**The metric you wish to aggregate. This will be one of the metrics formulas listed above.**Event:**Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.**Number of intervals:**The number of five-minute intervals, hours, days, weeks, or months to include in the rolling average. For example, a daily chart allows rolling averages over daily intervals only. The maximum ranges for a rolling average are 36 five-minute intervals (this works out to three hours), 72 hours, 90 days, 12 weeks, or 12 months.

Returns the metric for the event selected with a rolling window of however many days/weeks/months inputted, where a rolling window aggregation is applied* after* a cohort filter, if one is in use.

For example, this chart first calculates the new users for each time interval and then performs the rolling window aggregation on top of that.

### ROLLWINBEFORE

**Syntax**: ROLLWINBEFORE(metric, event, # of five-minute intervals/hours/days/weeks/months)

**Metric**: The metric you wish to aggregate. This will be one of the metrics formulas listed above.**Event**: Refers to the event you are interested in. This must be a letter that corresponds to an event in the Events card.**Number of intervals**: The number of five-minute intervals, hours, days, weeks, or months to include in the rolling average. For example, a daily chart allows rolling averages over daily intervals only. The maximum ranges for a rolling average are 36 five-minute intervals (this works out to three hours), 72 hours, 90 days, 12 weeks, or 12 months.

Returns the metric for the event selected with a rolling window of however many days/weeks/months inputted, where a rolling window aggregation is applied *before* a cohort filter, if one is in use.

For example, this chart first calculates the rolling active users for each time interval and then applies the new user cohort filter on top of that.

## Function formulas

### EXP

**Syntax: **EXP(value)

**Value:**The value can be a constant or another function (e.g. the value you pass in could be`UNIQUES`

of an event). The maximum value accepted is 700.

Returns e to the power of value you have specified. For example, here we are computing e to the power of the average number of times users purchase tickets.

### LN

**Syntax: LN**(value)

**Value:**The value can be a constant or another function (e.g. the value you pass in could be`UNIQUES`

of an event).

Returns the natural logarithm of the value. This is logarithm to the base of mathematical constant e. For example, `LN(UNIQUES(A))`

would calculate the natural logarithm of the number of unique users who triggered event A.

### LOG

**Syntax: **LOG(value, base)

**Value:**The value can be a constant or another function (e.g. the value you pass in could be`TOTALS`

of an event).**Base:**A constant. The base must be a constant and cannot contain another function.

Returns the logarithm of the value to the base. For example, the following formula will return the logarithm of the count of unique active users to base 3.

### LOG10

**Syntax: **LOG10(value)

**Value:**The value can be a constant or another function (e.g. the value you pass in could be`AVG`

of an event).

Returns the logarithm of the value to base 10. For example, the following formula will return the logarithm of the average number of times `Complete Purchase`

was triggered to base 10.

### POWER

**Syntax: **POWER(value, exponent)

**Value:**The value can be a constant or another function (e.g. the value you pass in could be`TOTALS`

of an event).**Exponent:**A constant. The exponent must be a constant and cannot contain another function.

Returns the inputted value to the power of the exponent specified. For example, `POWER(UNIQUES(A), 2)`

returns the number of unique users who triggered event A.

### SQRT

**Syntax: SQRT(value)**

**Value:**The value can be a constant or another function (e.g. the value you pass in could be AVG of an event).

Returns the square root of the value. For example, `SQRT(TOTALS(A))`

would return the square root of the total number of times users triggered event A.

### TRENDLINE

**Syntax: **TRENDLINE(value)

**Value:**The value can be a constant or another function (e.g. the value you pass in could be`UNIQUES`

of an event).

Returns the trendline of the value. This is calculated with ordinary least-squares linear regression. It is highly recommended that you plot another custom formula alongside this one, so you can compare them Otherwise, the `TRENDLINE`

function will simply give you a straight line with no context on a chart.

For example, use this function to see the trendline of number of users who purchase a song or video and compare it to the unique number of users.