Calculations - Tarvah

Overview

Tarvah’s calculation engine generates SQL window functions and aggregate expressions for common financial analytics patterns. Calculations are defined as a VDM transformation — the same level as expressions, aggregates, groupings, and time series — and automatically flow through to experiments and dashboards.

Supported Calculation Types

Type	SQL Pattern	Use Case
Percentage of Total	`value / NULLIF(SUM(value) OVER (PARTITION BY ...), 0)`	Position weight in account, sector allocation
Weighted Average	`SUM(value * weight) OVER (...) / NULLIF(SUM(weight) OVER (...), 0)`	Weighted yield, weighted ESG scores
Percentile	`PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY value) OVER (...)`	Median analytics, distribution analysis
Custom	User-defined SQL with `${field}` template references	Any custom formula

All calculations use NULLIF(..., 0) for safe division and COALESCE for null-safe arithmetic.

Define a Calculation on a VDM

Calculations are a VDM transformation type. Add them from the VDM editor alongside expressions, aggregates, groupings, and time series.

Open your VDM

Go to DataBridge → Virtual Data Marts and open the VDM you want to configure.

Add a Calculation transform

In the Transformations panel, click + and select Calculation.

Configure the calculation

Fill in the form:

Field Name — the name that will appear as a selectable measure in experiments
Calculation Type — Percentage of Total, Weighted Average, Percentile, or Custom SQL
Value Field — pick a numeric column from the VDM’s selected columns
Weight Field — (weighted average only) pick the weighting column
Partition By — select columns to group by (produces PARTITION BY in SQL)
Percentile — (percentile only) value between 0 and 1
Custom SQL — (custom only) raw SQL with ${columnName} references

The SQL Preview box shows the generated SQL in real time as you configure.

Save the VDM

Click Save. The calculation is now available as a measure in any experiment using this VDM.

The SQL preview runs entirely in the browser — no network calls needed. You see the exact SQL that will execute against your database.

Calculation Types in Detail

Percentage of Total

Calculates what fraction of a total a value represents, partitioned by one or more columns. Form fields: Value Field, Partition By Generated SQL:

"MKT_VAL" / NULLIF(SUM("MKT_VAL") OVER (PARTITION BY "ACCT_CD"), 0)

Example: If account ACC001 has total market value of

10M and position AAPL is

2M, the result is 0.20 (20%).

Weighted Average

Calculates a weighted average using a value field and a weight field. Form fields: Value Field, Weight Field, Partition By Generated SQL:

SUM(COALESCE("DURATION", 0) * COALESCE("MKT_VAL", 0)) OVER (PARTITION BY "ACCT_CD")
/ NULLIF(SUM(COALESCE("MKT_VAL", 0)) OVER (PARTITION BY "ACCT_CD"), 0)

Percentile

Calculates a percentile value within a partition. Form fields: Value Field, Percentile (0-1), Partition By Generated SQL:

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "ANALYTIC_VAL") OVER (PARTITION BY "ACCT_CD")

Custom SQL

Write arbitrary SQL with ${columnName} placeholders that resolve to the actual column references from your VDM. Form field: Custom SQL Example input:

${MKT_VAL} / NULLIF(${QTY}, 0)

Resolved SQL:

"FCT_POSITION"."MKT_VAL" / NULLIF("FCT_POSITION"."QTY", 0)

Calculation Object Structure

When working with calculations programmatically (e.g., in the calculationConfiguration array), use this structure:

{
  "id": "calc_pct_of_fund",
  "customFieldName": "% of Fund",
  "calculationType": "percentage_of_total",
  "valueField": "MKT_VAL",
  "partitionBy": ["ACCT_CD"],
  "dataType": "NUMBER"
}

Fields

Field	Type	Required	Description
`customFieldName`	string	Yes	Display name — appears as a selectable measure in experiments
`calculationType`	string	Yes	One of: `percentage_of_total`, `weighted_average`, `percentile`, `custom`
`valueField`	string	Conditional	Column to calculate on. Required for all types except `custom`.
`weightField`	string	Conditional	Weighting column. Required for `weighted_average`.
`percentile`	number	Conditional	Value between 0 and 1. Required for `percentile`.
`partitionBy`	string[]	No	Columns to partition by. Produces `PARTITION BY` in SQL.
`customSql`	string	Conditional	Raw SQL expression. Required for `custom`. Supports `${columnName}` references.

Example: Alpha Core Portfolio Calculations

This walkthrough uses the Alpha Core model (FCT_POSITION_SECURITY_LEVEL with DIM_ACCOUNT and DIM_SECURITY joins) to set up three common financial calculations.

Step 1: Open the VDM

Open the VDM that was created from the Alpha Core LookML import. Go to DataBridge → Virtual Data Marts → select the VDM.

Step 2: Add Weighted Average ESG Score

Add Calculation

Click + in the Transformations panel → select Calculation.

Configure

Field Name: Wtd Avg ESG Score
Calculation Type: Weighted Average
Value Field: ESG_SCORE
Weight Field: MKT_VAL
Partition By: ACCT_CD

Verify preview

The SQL Preview should show:

SUM(COALESCE("ESG_SCORE", 0) * COALESCE("MKT_VAL", 0)) OVER (PARTITION BY "ACCT_CD")
/ NULLIF(SUM(COALESCE("MKT_VAL", 0)) OVER (PARTITION BY "ACCT_CD"), 0)

Save

Click Save.

Step 3: Add % of Fund

Add Calculation

Click + → Calculation.

Configure

Field Name: % of Fund
Calculation Type: Percentage of Total
Value Field: MKT_VAL
Partition By: ACCT_CD

Save

Click Save.

Step 4: Add Median Analytic Value

Add Calculation

Click + → Calculation.

Configure

Field Name: Median Analytic
Calculation Type: Percentile
Value Field: ANALYTIC_VAL
Percentile: 0.5
Partition By: ACCT_CD

Save

Click Save.

Step 5: Save the VDM

Click Save on the VDM. All three calculations are now available as measures in experiments and SDK imports.

Step 6: Use in an experiment

Create an experiment from the VDM. Drag Wtd Avg ESG Score onto the Y axis and Account Code onto the X axis. The chart shows the market-value-weighted ESG score per account — not a simple average.

Using Calculations in Experiments

Once a calculation is saved on the VDM, it appears as a selectable measure when building experiments:

Create an experiment from the VDM
In the chart configuration, drag the calculation field (e.g., ”% of Fund”) onto the Y axis
Set aggregation to as-is — the calculation’s SQL is used directly

Calculations produce operationType: "EXPRESSION" in the query engine, which means the full SQL window function is placed directly in the SELECT clause. This works across all 5 supported databases: Snowflake, Postgres, Oracle, IBM DB2, and SQL Server.

BI Import

LookML models define raw building blocks — dimensions (ACCT_CD, MKT_VAL, ESG_SCORE) and standard measures (SUM, AVG, COUNT). Complex calculations like weighted averages and percentiles are not defined in LookML — they are configured on the VDM after import.

LookML Model (dimensions + standard measures)
    -> BI Import Adapter (translates to VDM selectedColumns + measureConfiguration)
        -> VDM (raw columns available)
            -> VDM Editor: Add Calculation transforms (weighted avg, % of total, etc.)
                -> measureConfiguration (EXPRESSION type for query engine)
                    -> Experiment / Dashboard (renders in charts)

After importing a LookML model into a VDM, open the VDM editor and add Calculation transforms to define your complex analytics. The calculations reference the columns that were imported from LookML.

​Overview

​Supported Calculation Types

​Define a Calculation on a VDM

​Calculation Types in Detail

​Percentage of Total

​Weighted Average

​Percentile

​Custom SQL

​Calculation Object Structure

​Fields

​Example: Alpha Core Portfolio Calculations

​Step 1: Open the VDM

​Step 2: Add Weighted Average ESG Score

​Step 3: Add % of Fund

​Step 4: Add Median Analytic Value

​Step 5: Save the VDM

​Step 6: Use in an experiment

​Using Calculations in Experiments

​BI Import

Overview

Supported Calculation Types

Define a Calculation on a VDM

Calculation Types in Detail

Percentage of Total

Weighted Average

Percentile

Custom SQL

Calculation Object Structure

Fields

Example: Alpha Core Portfolio Calculations

Step 1: Open the VDM

Step 2: Add Weighted Average ESG Score

Step 3: Add % of Fund

Step 4: Add Median Analytic Value

Step 5: Save the VDM

Step 6: Use in an experiment

Using Calculations in Experiments

BI Import