Metric Formulas

Modeling formulas are designed to perform calculations on metrics over specified time periods, enabling you to see progress and changes over time.

Aggregation Formulas

Aggregation formulas allow you to perform calculations on a range of data within a column. These functions are essential for summarizing large datasets and gaining quick insights.

Available Functions

Function
Syntax
Description

SUM

=SUM(AggColumn,DateColumn)

Calculates the total sum of all numeric values in the specified aggregated column, then summarizes into periods of selected date column.

AVG

=AVG(AggColumn,DateColumn)

Computes the average (mean) of numeric values in the aggregated column, then summarizes into periods of selected date column.

COUNT

=COUNT(AggColumn,DateColumn)

Counts the number of non-empty cells in the aggregated column, then summarizes into periods of selected date column.

COUNTD

=COUNTD(AggColumn,DateColumn)

Counts the number of distinct (unique) non-empty values in the aggregated column, then summarizes into periods of selected date column.

MIN

=MIN(AggColumn,DateColumn)

Finds the minimum (smallest) value in the aggregated column, then summarizes into periods of selected date column.

MAX

=MAX(ColumnNamAggColumn,DateColumn)

Finds the maximum (largest) value in the aggregated column, then summarizes into periods of selected date column.

FIRST

=FIRST(AggColumn,DateColumn)

Retrieves the first value in the aggregated column based on the data's order, then summarizes into periods of selected date column.

LAST

=LAST(AggColumn,DateColumn)

Retrieves the last value in the aggregated column based on the data's order, then summarizes into periods of selected date column.

Examples

  • Sum of Sales: =SUM(Sales,Order Date) adds up all sales figures in the "Sales" column.

  • Average Temperature: =AVG(Temperature,Record Date) calculates the mean temperature from the "Temperature" column.

  • Total Entries: =COUNT(UserID,Registration Date) counts how many user IDs are present.

  • Unique Products Sold: =COUNTD(ProductID,Created Date) counts the number of unique products sold.

  • Lowest Score: =MIN(TestScores,Exam Date) finds the smallest test score in the "TestScores" column.

  • Highest Revenue: =MAX(Revenue,Invoice Date) identifies the highest revenue figure.

  • First Entry Date: =FIRST(Order Date,Order Date) retrieves the earliest date in the "Order Date" column.

  • Most Recent Update: =LAST(UpdateTimestamp,Update Timestamp) gets the latest timestamp from the "UpdateTimestamp" column.

Formulas on top of Existing Metrics

The below functions are designed to perform calculations on metrics over specified time periods, giving you more power to build on top of existing truths and gain deeper insights.

Available Functions

Function
Syntax
Description

SUM

=SUM(Metric1(t),Metric2(t))

Calculates the sum of N metrics over the same time range.

AVG

=AVG(Metric1(t),Metric2(t))

Calculates the average of N metrics over the same time range. *Note: The result will reflect Total Sum / Number of Metrics included

GROWTH

=GROWTH(MetricName(t))

Calculates period-over-period growth using the formula (this period - last period) / last period.

CUMULATIVE

=CUMULATIVE(MetricName(t))

Calculates the cumulative total of a metric up to time t, aggregating all previous periods.

Arithmetic Calculations

=MetricName1(t) [operator] MetricName2(t)

Perform arithmetic operations between metrics over time periods.

Arithmetics Operators

  • Addition (+): Adds two values.

  • Subtraction (-): Subtracts the second value from the first.

  • Multiplication (*): Multiplies two values.

  • Division (/): Divides the first value by the second.

Syntax

  • =MetricName1(t) [operator] MetricName2(t)

Examples

  • Profit Over Time: =Revenue(t) - Costs(t) computes profit for the current period.

  • Average Revenue per User: =Revenue(t) / ActiveUsers(t) calculates the average revenue per active user at time t.

  • Conversion Rate: =Conversions(t) / Visitors(t) determines the conversion rate at time t.

Understanding Time Expression t

The time expression t is a crucial component in modeling formulas, allowing you to specify the time frame for your calculations.

Definitions:

  • t: The current time period or a specific time point (e.g., the current month, quarter, or year).

  • t-m: A past time period, where m represents the number of periods before t.

  • t-m:t-n: A range of time periods from t-m (earlier) to t-n (later), useful for aggregations over time.

Examples:

  • Current Quarter: If t is Q4 2023, then t refers to Q4 2023.

  • Previous Month: t-1 would refer to one period before t, such as the previous month.

  • Year-to-Date: t-11:t could represent the past 12 months including the current period.

Advanced Metric Definition: Metrics with Filters

In some cases, you may need to define a metric that includes specific filtering conditions. For example, you might want to count only successful orders, exclude certain categories, or focus on a particular customer segment. Presight allows you to apply filters directly to metrics, enabling more precise and targeted analysis.

Applying Filters to Metrics

To add a filter to a metric:

  1. Select the Metric: Enter edit mode of the metric by double-clicking on the cell values in a metric table.

  2. Access the Filter Menu: Click on the filter icon or menu associated with the metric. If you have an advance condition, click on the menu Advanced Options instead.

  3. Define Filter Conditions: Specify the conditions that data must meet to be included in the metric calculation.

    • Field: Select the field or dimension you want to filter on (e.g., Order Status).

    • Operator: Choose the operator that defines how the filter is applied (e.g., =, >, <, IN, CONTAINS).

    • Value: Enter the value or values that the field should match (e.g., "Success").

  4. Apply the Filter: Confirm the filter conditions to apply them to the metric.

Examples

  • Counting Successful Orders:

    • Metric: Orders

    • Filter: Order Status = "Success"

    • Formula: =COUNT(OrdersID,Order Date) + filter Order Status = Success

  • Number of High-Value Transactions:

    • Metric: Transactions

    • Filter: Amount > 1000

    • Formula: =COUNT(Transactions, Transaction Date) + filter Amount > 1000

  • Total Revenue from VIP Customers:

    • Metric: Revenue

    • Filter: Customer Tier = "VIP", Order Status = "Success"

    • Formula: =SUM(Revenue, Invoice Date) + filter Customer Tier = VIP AND + filter Order Status = Success

Benefits of Using Filters

  • Targeted Analysis: Focus on specific subsets of your data that are most relevant to your analysis.

  • Dynamic Metrics: Easily adjust filters to explore different scenarios without creating new metrics.

  • Improved Accuracy: Ensure that calculations only include data that meets your defined criteria.

Combining Filters with Formulas

You can combine filters with other formulas to perform complex calculations. For instance:

Conversion Rate for a Campaign:

  • Formula:

=(Conversions(t) + filter City = Seattle

/ Visitors(t)) + filter Campaign = Spring Sales

* 100

Managing Filters

  • Editing Filters: Modify existing filters by accessing the filter menu and adjusting the conditions.

  • Removing Filters: To remove a filter, delete the condition from the filter menu or deselect it.

  • Multiple Conditions: Apply multiple filter conditions using logical operators like AND and OR.

    • Example: Order Status = "Success" AND Payment Method = "Credit Card"

    • Formula: =COUNT(Orders).OrderStatus("Success").PaymentMethod("Credit Card")

PreviousFormulasNextData Transformation Formulas

Last updated