Drivetrain Knowledge Hub
  • Getting started
    • Start here
      • Navigating Drivetrain
      • Key terms & concepts
    • Reports
      • Interacting with reports
      • Creating a report
      • Collaborating on a report
    • Data
      • Connecting data sources
      • Explore your datasets
      • Transforming and enriching data
    • Metrics
      • Metric Builder overview
      • Creating a metric from a dataset
      • Creating a metric using formula
      • Adding dimensions
    • Models
  • Modeling
    • Models page overview
    • Creating a model
    • Formatting a variable
    • Adding plan/forecast numbers to a variable
    • Lists overview
    • Adding dimensions to a model
    • Saving and managing versions
    • Creating and comparing scenarios
    • Setting up reconciliation workflows
    • Collaborating on models (commenting)
    • More model elements
    • Practical example
  • Formulas
    • Formulas in Drivetrain
      • Referencing metrics in formulas
      • Operators
      • Functions
        • ABS
        • AND
        • AVERAGE
        • BLANK
        • BOMONTH
        • CEIL
        • COLLAPSE
        • CONCAT
        • DATE
        • DATEADD
        • DATEDIFF
        • DATEVALUE
        • DAY
        • DAYSINMONTH
        • EOMONTH
        • EQUALS (or EQ)
        • EVEN
        • FINANCIALYEARSTARTMONTH
        • FLOOR
        • FORECAST
        • IF
        • ISBLANK
        • LAST ACTUALS DATE
        • LOOKUP
        • LOWER
        • MAX
        • MID
        • MIN
        • MOD
        • MONTH
        • NETWORKDAYS
        • NOT
        • ODD
        • OR
        • PERIOD
        • PRORATA
        • QUARTERTODATE
        • ROUND
        • ROUNDDOWN
        • ROUNDUP
        • SLN
        • SUM
        • SUMMARIZE
        • THIS MONTH
        • TODAY
        • VALUE
        • UPPER
        • YEAR
        • YEARTODATE
  • User Administration and Access
    • Access control & permissions
      • Creating a user
      • Creating and managing roles
      • Understanding permissions in roles
      • Reviewing user permissions
    • Data masking
  • Data Governance
    • How Drivetrain ensures data quality
  • Integration guide
    • Integrate with Drivetrain
  • 📡Extending Drivetrain
    • Drivetrain API
      • Get metric list API
      • Get metric data API
    • Export to data warehouses
      • Export to BigQuery
    • Google Sheets add-on
    • Integration for Slack
Powered by GitBook
On this page

Was this helpful?

  1. Formulas
  2. Formulas in Drivetrain

Referencing metrics in formulas

PreviousFormulas in DrivetrainNextOperators

Last updated 6 months ago

Was this helpful?

As you start typing in a metric or variable name in the Formula Builder (a text box which accepts formulas), the autocomplete dropdown will show you a list of options from which you can select the right one.

Once you have selected the metric/variable, you can select one or more of the following:

  1. a specific time interval

  2. a specific scenario

  3. a specific dimension

for that metric.

1. A specific time interval

The time property is quite flexible and provides you the ability to specify the period in fixed, span or relative time intervals.

Fixed

You can refer to a metric’s value for a specific period throughout by using the Custom option and specify the month as “±n” from the start of the planning period.

For example, if the planning period is from January 2024 to December 2024, then a value of “-3” in the Custom option for a metric in the January 2024 cell would refer to the October 2023 value of the metric.

Span

A time period (span) can be referred to in formulas as well. Drivetrain supports ‘Quarter’ and ‘Year’ options besides ‘Previous month’, ‘This month’ and ‘Next month’.

For example, inputting Salary[Previous Quarter] will aggregate the salaries for the 3 months in the preceding quarter.

Relative

You can use the Custom option to specify a time period as “t ± n”, “q ± n”, or “y ± n” option, where ‘t’, ‘q’ and ‘y’ refer to the current month, current quarter, and current year, while ‘n’ refers to the number of periods.

Take an example where the average time taken to convert a Lead to a Marketing Qualified Lead (MQL) is three months, then to compute the number of MQLs, you will need to refer to the number of leads from three months prior and multiply it by average lead to MQL conversion rate. This is given by:

MQLs = Leads[t-3] * Lead → MQL conversion rate

Thus for April 2024, the above formula takes the number of leads in January 2024 and multiply it with the Lead → MQL conversion rate for April.

Last actuals date

You can use the Last actuals date option to get the value from the last available 'actual' value for that metric.

2. A specific scenario

You can select a scenario.

3. A specific dimension

You can select a specific dimension.