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.
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)
below refers to the event View Item Details
while the letter B in the formula UNIQUES(B)
below 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
andTUTORIAL
(the matching is case sensitive) 
1
and1.0
(nonmatching 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
The below tables list 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 colorcoded 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 
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 colorcoded 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. The below 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 ecommerce 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.
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)
. The setup shown below 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.
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 groupby clause.
Returns the average of the property values you are grouping by. This function is equivalent to PROPSUM(event)/TOTALS(event)
. 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 the example below, 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 groupby 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 groupby 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 groupby 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 groupby 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.
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. The setup below 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.
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, the chart below shows you 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 fiveminute 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 fiveminute 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.
Below, the blue line 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 fiveminute 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 fiveminute 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 fiveminute 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 fiveminute 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 fiveminute 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 fiveminute 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 leastsquares 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.