Financial consolidation

This tutorial consolidates revenue across legal entities that use different local rules, then removes intercompany activity from the group result.

The important lesson is ordering: calculate each leaf under its own policy first, add the leaves second, and apply eliminations last. Shared mappings and rule features keep the country-specific differences in data rather than separate formula copies.

This advanced tutorial assumes the relationship model from E-commerce and persisted features from SaaS metrics . Run Data, Model, and the revenue library before the consolidation queries.

Meet the consolidation data

Europe Ops contains two leaf entities, France Unit and Germany Unit. Their local revenue policies differ:

France Unit excludes shipping and includes returns.
Germany Unit includes shipping and excludes returns.

Transactions are tagged by account and month. February also contains two sales between France and Germany that must disappear at the Europe level.

FeatureQL

CREATE SCHEMA IF NOT EXISTS tutorial_finance;
--
DROP TABLE IF EXISTS tutorial_finance.intercompany;
--
DROP TABLE IF EXISTS tutorial_finance.transactions;
--
DROP TABLE IF EXISTS tutorial_finance.revenue_rules;
--
DROP TABLE IF EXISTS tutorial_finance.entities;
--
CREATE TABLE tutorial_finance.entities (
  entity_id BIGINT,
  name VARCHAR,
  parent_id BIGINT,
  currency VARCHAR
);
--
INSERT INTO tutorial_finance.entities VALUES
  (1, 'Europe Ops', NULL, 'EUR'),
  (2, 'France Unit', 1, 'EUR'),
  (3, 'Germany Unit', 1, 'EUR');
--
CREATE TABLE tutorial_finance.revenue_rules (
  entity_id BIGINT,
  includes_shipping BOOLEAN,
  includes_returns BOOLEAN
);
--
INSERT INTO tutorial_finance.revenue_rules VALUES
  (2, false, true),
  (3, true, false);
--
CREATE TABLE tutorial_finance.transactions (
  tx_id BIGINT,
  tx_entity_id BIGINT,
  account VARCHAR,
  amount DOUBLE,
  tx_date DATE
);
--
INSERT INTO tutorial_finance.transactions VALUES
  (1, 2, 'gross_sales', 30000e0, DATE '2024-01-10'),
  (2, 2, 'shipping_revenue', 1500e0, DATE '2024-01-10'),
  (3, 2, 'returns', -1000e0, DATE '2024-01-25'),
  (4, 3, 'gross_sales', 40000e0, DATE '2024-01-12'),
  (5, 3, 'shipping_revenue', 2500e0, DATE '2024-01-12'),
  (6, 3, 'returns', -3000e0, DATE '2024-01-28'),
  (7, 2, 'gross_sales', 28000e0, DATE '2024-02-10'),
  (8, 2, 'returns', -800e0, DATE '2024-02-25'),
  (9, 3, 'gross_sales', 45000e0, DATE '2024-02-12'),
  (10, 3, 'shipping_revenue', 3000e0, DATE '2024-02-12'),
  (11, 3, 'returns', -2000e0, DATE '2024-02-28');
--
CREATE TABLE tutorial_finance.intercompany (
  interco_id BIGINT,
  from_entity_id BIGINT,
  to_entity_id BIGINT,
  account VARCHAR,
  amount DOUBLE,
  tx_date DATE
);
--
INSERT INTO tutorial_finance.intercompany VALUES
  (1, 2, 3, 'interco_sale', 8000e0, DATE '2024-02-15'),
  (2, 3, 2, 'interco_sale', 6000e0, DATE '2024-02-20');
--
SELECT CAST(COUNT() AS INTEGER) AS cnt FROM tutorial_finance.transactions;

Result

Count BIGINT
11

The policy difference is stored as data. That lets one revenue formula behave differently for each entity without branching into country-specific copies.

Build the hierarchy and mappings

Declare the consolidation entities and the month input used by every query:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.FINANCE AS
PERSIST
    fc_entities := ENTITY(),
    entity_id := INPUT(BIGINT#fc_entities),
    transactions := ENTITY(),
    tx_id := INPUT(BIGINT#transactions),
    interco := ENTITY(),
    interco_id := INPUT(BIGINT#interco),
    month_input := INPUT(VARCHAR)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.FINANCE.FC_ENTITIES	CREATED	Feature created as not exists
FM.FINANCE.ENTITY_ID	CREATED	Feature created as not exists
FM.FINANCE.TRANSACTIONS	CREATED	Feature created as not exists
FM.FINANCE.TX_ID	CREATED	Feature created as not exists
FM.FINANCE.INTERCO	CREATED	Feature created as not exists
FM.FINANCE.INTERCO_ID	CREATED	Feature created as not exists
FM.FINANCE.MONTH_INPUT	CREATED	Feature created as not exists

Then map entity metadata, local rules, transactions, and intercompany rows:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.FINANCE AS
PERSIST
    tables.dim_entities := EXTERNAL_COLUMNS(
        entity_id BIGINT#fc_entities BIND TO entity_id,
        name VARCHAR,
        parent_id BIGINT,
        currency VARCHAR
        FROM TABLE(tutorial_finance.entities)
    ),
    tables.rules := EXTERNAL_COLUMNS(
        entity_id BIGINT#fc_entities BIND TO entity_id,
        includes_shipping BOOLEAN,
        includes_returns BOOLEAN
        FROM TABLE(tutorial_finance.revenue_rules)
    ),
    tables.fct_tx := EXTERNAL_COLUMNS(
        tx_id BIGINT#transactions BIND TO tx_id,
        tx_entity_id BIGINT#fc_entities,
        account VARCHAR,
        amount DOUBLE,
        tx_date DATE
        FROM TABLE(tutorial_finance.transactions)
    ),
    tables.interco := EXTERNAL_COLUMNS(
        interco_id BIGINT#interco BIND TO interco_id,
        from_entity_id BIGINT#fc_entities,
        to_entity_id BIGINT#fc_entities,
        account VARCHAR,
        amount DOUBLE,
        tx_date DATE
        FROM TABLE(tutorial_finance.intercompany)
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.FINANCE.TABLES.DIM_ENTITIES	CREATED	Feature created as not exists
FM.FINANCE.TABLES.RULES	CREATED	Feature created as not exists
FM.FINANCE.TABLES.FCT_TX	CREATED	Feature created as not exists
FM.FINANCE.TABLES.INTERCO	CREATED	Feature created as not exists

Persist the reusable revenue building blocks, including the MONTH_INPUT used by each monthly query:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.FINANCE AS
PERSIST
    entity_name := tables.dim_entities[name],
    includes_shipping := tables.rules[includes_shipping],
    includes_returns := tables.rules[includes_returns],
    tx_account := tables.fct_tx[account],
    tx_amount := tables.fct_tx[amount],
    tx_month := DATE_FORMAT(tables.fct_tx[tx_date]::TIMESTAMP, '%Y-%m'),
    tx_entity_id := tables.fct_tx[tx_entity_id],
    ic_month := DATE_FORMAT(tables.interco[tx_date]::TIMESTAMP, '%Y-%m')
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.FINANCE.ENTITY_NAME	CREATED	Feature created as not exists
FM.FINANCE.INCLUDES_SHIPPING	CREATED	Feature created as not exists
FM.FINANCE.INCLUDES_RETURNS	CREATED	Feature created as not exists
FM.FINANCE.TX_ACCOUNT	CREATED	Feature created as not exists
FM.FINANCE.TX_AMOUNT	CREATED	Feature created as not exists
FM.FINANCE.TX_MONTH	CREATED	Feature created as not exists
FM.FINANCE.TX_ENTITY_ID	CREATED	Feature created as not exists
FM.FINANCE.IC_MONTH	CREATED	Feature created as not exists

The library keeps local policy close to the entity it governs. The consolidation queries can therefore focus on rollup rather than repeat shipping and return conditions.

Apply local rules in January

Bind January and calculate revenue independently for France and Germany:

FeatureQL

DEFINE
    gross := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'gross_sales' AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    shipping := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'shipping_revenue'
            AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    returns_amt := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'returns' AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    revenue := gross + IF(includes_shipping, shipping, 0e0)
    + IF(includes_returns, returns_amt, 0e0)
RETURN
    entity_name,
    revenue
FROM FM.FINANCE
FOR
    month_input := BIND_VALUE('2024-01'),
    entity_id := BIND_VALUES(ARRAY(2, 3)),
    NESTED tx_id := BIND_COLUMNS(
        tx_id
        FROM SQL(SELECT tx_id FROM tutorial_finance.transactions ORDER BY tx_id)
    )
ORDER BY entity_name
;

Result

ENTITY_NAME VARCHAR	REVENUE DOUBLE
France Unit	29000.0
Germany Unit	42500.0

France Unit reports 29000 EUR: 30000 - 1000 of returns, with shipping ignored. Germany Unit reports 42500 EUR: 40000 + 2500 of shipping, with returns ignored.

Both rows come from the same feature definition. The entity's mapped policy determines which components count.

Reuse the definition in February

Change only the month binding:

FeatureQL

DEFINE
    gross := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'gross_sales' AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    shipping := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'shipping_revenue'
            AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    returns_amt := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'returns' AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    revenue := gross + IF(includes_shipping, shipping, 0e0)
    + IF(includes_returns, returns_amt, 0e0)
RETURN
    entity_name,
    revenue
FROM FM.FINANCE
FOR
    month_input := BIND_VALUE('2024-02'),
    entity_id := BIND_VALUES(ARRAY(2, 3)),
    NESTED tx_id := BIND_COLUMNS(
        tx_id
        FROM SQL(SELECT tx_id FROM tutorial_finance.transactions ORDER BY tx_id)
    )
ORDER BY entity_name
;

Result

ENTITY_NAME VARCHAR	REVENUE DOUBLE
France Unit	27200.0
Germany Unit	48000.0

France Unit now reports 27200 EUR and Germany Unit 48000 EUR. The local formula and policy mappings are unchanged.

Roll January up to Europe

At the parent level, first sum the leaf results. January has no intercompany rows, so the gross and consolidated totals are the same:

FeatureQL

DEFINE
    gross := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'gross_sales' AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    shipping := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'shipping_revenue'
            AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    returns_amt := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'returns' AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    revenue := gross + IF(includes_shipping, shipping, 0e0)
    + IF(includes_returns, returns_amt, 0e0),
    consolidated_revenue_eur := SUM(revenue) PER entity_id
RETURN
    consolidated_revenue_eur
FROM FM.FINANCE
FOR
    month_input := BIND_VALUE('2024-01'),
    entity_id := BIND_VALUES(ARRAY(2, 3)),
    NESTED tx_id := BIND_COLUMNS(
        tx_id
        FROM SQL(SELECT tx_id FROM tutorial_finance.transactions ORDER BY tx_id)
    )
;

Result

CONSOLIDATED_REVENUE_EUR DOUBLE
71500.0

The Europe result is 71500 EUR, exactly 29000 + 42500.

Eliminate February's internal sales

February's leaf total is 75200 EUR, but it includes two sales between France Unit and Germany Unit: 8000 and 6000. Those transactions are real for the leaves and invalid as external Europe revenue.

FeatureQL

DEFINE
    gross := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'gross_sales' AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    shipping := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'shipping_revenue'
            AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    returns_amt := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'returns' AND tx_month = month_input)
            GROUP BY (tx_entity_id)
        ),
        0e0
    ),
    revenue := gross + IF(includes_shipping, shipping, 0e0)
    + IF(includes_returns, returns_amt, 0e0),
    interco_elim := COALESCE(
        entity_id.RELATED(
            SUM(tables.interco[amount])
            FILTER (WHERE tables.interco[account] = 'interco_sale'
            AND ic_month = month_input)
            GROUP BY (tables.interco[to_entity_id])
        ),
        0e0
    ),
    consolidated_revenue_eur := SUM(revenue - interco_elim) PER ROW
RETURN
    consolidated_revenue_eur
FROM FM.FINANCE
FOR CROSS
    month_input := BIND_VALUE('2024-02'),
    entity_id := BIND_VALUES(ARRAY(2, 3)),
    NESTED tx_id := BIND_COLUMNS(
        tx_id
        FROM SQL(SELECT tx_id FROM tutorial_finance.transactions ORDER BY tx_id)
    ),
    NESTED interco_id := BIND_COLUMNS(
        interco_id
        FROM SQL(SELECT interco_id FROM tutorial_finance.intercompany ORDER BY interco_id)
    )
;

Result

CONSOLIDATED_REVENUE_EUR DOUBLE
61200.0

The consolidated result is:

75200 - (8000 + 6000) = 61200 EUR

This is why eliminations are non-additive: summing correct leaf measures is not sufficient at the parent grain. See Metric additivity for the distinction between a legal rollup and a new target-grain formula.

What's next

You have applied data-driven rules at the leaf grain, reused them across months, and added a parent-level adjustment that cannot be pushed into a simple sum.

Continue with Marketing attribution for another allocation problem, or return to the analytics overview to compare techniques.

Last update at: 2026/10/13 08:32:21

On this page

Meet the consolidation data Build the hierarchy and mappings Apply local rules in January Reuse the definition in February Roll January up to Europe Eliminate February's internal sales What's next