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
  3. Functions

COLLAPSE

PreviousCEILNextCONCAT

Last updated 6 months ago

Was this helpful?

Aggregates the values in a metric by its aggregation summary type (sum, average or formula) after eliminating (i.e, collapsing) the specified dimensions and aggregating across the rest.

In simple terms, the function takes a metric name, and one or more dimension names, then returns the summary across the other dimensions. It collapses (i.e, removes) the dimensions specified while computing the summary.

Syntax: COLLAPSE ( metric_or_variable, dimension1, dimension2,... )

  • metric_or_variable is the name of the metric or variable

  • dimension1 , dimension2, ... (optional) are the dimension names you want to collapse and then aggregate by the remaining dimensions

For example, consider the Revenue metric for the period of Jul '23 to Sep '23.

The Revenue metric contains three nested dimensions — Region, Country and Territory. The dimension values for Region are APAC, Europe and North America. Each Region has Countries and each Country has Territories.

Example to calculate the Revenue by country:

The collapse function can be used to calculate the revenue of each country by "collapsing" the Region and Territory dimensions. For example, Canada's Revenue is $1,050.

Let's create a new metric Revenue by Country and use the collapse function to compute the values.

Revenue by Country = COLLAPSE ( Revenue, Region, Territory )

Output:

Example to calculate the Revenue share of a country to the overall revenue:

Create a new metric Revenue Share - Country to Overall Revenue .

Revenue Share - Country to Overall Revenue = Revenue of a Country / Total Revenue of Business

For example, Canada's share of the Revenue is $1,050 / $12,800 = 8.2% , France's revenue share is $1,800 / $12,800 = 14.06% and so on.

To get the Revenue of a Country in the numerator, we first add Country as a dimension to this new metric.

Since we want the Total Revenue in the denominator, we "collapse" all the three dimensions (Region, Country and Territory).

Revenue Share - Country to Overall Revenue = Revenue / COLLAPSE ( Revenue, Country, Region, Territory )

Output:

Example to calculate the Revenue share of a country to its region:

Create a new metric Revenue Share - Country to Region .

Revenue Share - Country to Region = Revenue of a Country / Total Revenue of Region

For example, Canada's share of the Revenue to its Region is $1,050 / $6,600 = 15.91% , France's revenue share is $1,800 / $4,000 = 45% and so on.

To get the the Revenue of a Country in the numerator, we first add Country as a dimension to this new metric.

Since we want the Revenue of a Region in the denominator, we "collapse" all dimensions but for Region (Country and Territory).

Revenue Share - Country to Region = Revenue / COLLAPSE ( Revenue, Country, Territory )

Output:

This is the same as using Collapse function but instead of removing the dimension we want to aggregate the data on, we specify the dimension: Revenue Share - Country to Region = Revenue / SUMMARIZE ( Revenue, Region )

Related:

Summarize