Calculations - Tarvah

Overview

Tarvah’s calculation engine generates SQL window functions and aggregate expressions for common financial analytics patterns. Calculations are defined on the semantic model and automatically flow through to VDMs, 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.

Pre-built Templates

Nine templates are available out of the box. Templates are starting points, not constraints — they pre-populate the calculation type and suggest default field names (e.g., mkt_val, acct_cd), but you can override every field to match your model’s actual column names. The calculation engine resolves whatever field names you provide against your semantic model’s dimensions and measures.

Default fields are suggestions, not requirements. Every template shows default field names like mkt_val and acct_cd — these are pre-filled when you select the template but can be changed to any field in your model. The SQL pattern (percentage, weighted average, etc.) stays the same; only the column references change.

Percentage Templates

pct_position_in_account — % of position within account

What it calculates: What percentage of the account’s total market value does this position represent?Default fields: valueField: mkt_val, partitionBy: [acct_cd]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%).

pct_category_in_account — % of category within account

What it calculates: What percentage of the account does a particular category (e.g., sector) represent?Default fields: valueField: mkt_val, partitionBy: [acct_cd]Tip: Override partitionBy to ["acct_cd", "sector"] for intra-category percentages.Generated SQL (with sector partition):

mkt_val / NULLIF(SUM(mkt_val) OVER (PARTITION BY acct_cd, sector), 0)

pct_account_in_group — % of account within account group

What it calculates: What percentage of the account group’s total does this account represent?Default fields: valueField: mkt_val, partitionBy: [acct_group]Generated SQL:

mkt_val / NULLIF(SUM(mkt_val) OVER (PARTITION BY acct_group), 0)

Weighted Average Templates

wavg_analytics_in_fund — Weighted average of analytics within fund

What it calculates: Market-value-weighted average of an analytic value (e.g., yield, duration) across a fund.Default fields: valueField: analytic_val, weightField: mkt_val, partitionBy: [acct_cd]Generated SQL:

SUM(COALESCE(analytic_val, 0) * COALESCE(mkt_val, 0)) OVER (PARTITION BY acct_cd)
/ NULLIF(SUM(COALESCE(mkt_val, 0)) OVER (PARTITION BY acct_cd), 0)

Example: For ACC001 with positions weighted by market value, the result is the portfolio-weighted average of analytic_val.

wavg_analytics_by_category — Weighted average by category

What it calculates: Same as above, but partitioned by both account and category (sector).Default fields: valueField: analytic_val, weightField: mkt_val, partitionBy: [acct_cd, sector]Generated SQL:

SUM(COALESCE(analytic_val, 0) * COALESCE(mkt_val, 0)) OVER (PARTITION BY acct_cd, sector)
/ NULLIF(SUM(COALESCE(mkt_val, 0)) OVER (PARTITION BY acct_cd, sector), 0)

wavg_issuer_analytics — Weighted average of issuer-level analytics (ESG)

What it calculates: Market-value-weighted average of issuer-level metrics (e.g., ESG scores) within a fund.Default fields: valueField: esg_score, weightField: mkt_val, partitionBy: [acct_cd]Prerequisite: The semantic model must include a join to the issuer dimension table with esg_score defined.

wavg_issuer_by_category — Weighted average of issuer analytics by category

What it calculates: ESG or issuer metrics weighted by market value, partitioned by account and sector.Default fields: valueField: esg_score, weightField: mkt_val, partitionBy: [acct_cd, sector]

Percentile Templates

pctl_analytics_in_fund — Percentile of analytics within fund

Default fields: valueField: analytic_val, partitionBy: [acct_cd], percentile: 0.5 (median)Generated SQL:

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY analytic_val) OVER (PARTITION BY acct_cd)

pctl_analytics_by_category — Percentile of analytics by category

Default fields: valueField: analytic_val, partitionBy: [acct_cd, sector], percentile: 0.5

Calculation Object Structure

When creating calculations via the API or the customCalculations array in onboarding, use this structure:

{
  "name": "pct_of_fund",
  "label": "% of Fund",
  "type": "measure",
  "calculationType": "percentage_of_total",
  "valueField": "mkt_val",
  "partitionBy": ["acct_cd"],
  "description": "Position market value as percentage of fund total"
}

Fields

Field	Type	Required	Description
`name`	string	Yes	Unique identifier within the model
`label`	string	No	Display label (defaults to `name`)
`type`	string	No	Always `"measure"` for calculations
`calculationType`	string	Yes	One of: `percentage_of_total`, `weighted_average`, `percentile`, `custom`
`valueField`	string	Conditional	The field to calculate on. Required for all types except `custom`.
`weightField`	string	Conditional	The weighting field. Required for `weighted_average`.
`percentile`	number	Conditional	Value between 0 and 1. Required for `percentile`.
`partitionBy`	string[]	No	Fields to partition (group) by. Produces `PARTITION BY` in SQL.
`sql`	string	Conditional	Raw SQL expression. Required for `custom`. Supports `${fieldName}` references.
`templateId`	string	No	Apply a pre-built template (auto-populates defaults).
`description`	string	No	Human-readable description.

Weighted Average Example

{
  "name": "wavg_duration",
  "label": "Weighted Avg Duration",
  "calculationType": "weighted_average",
  "valueField": "duration",
  "weightField": "mkt_val",
  "partitionBy": ["acct_cd"]
}

Custom SQL Example

{
  "name": "spread_to_benchmark",
  "label": "Spread to Benchmark",
  "calculationType": "custom",
  "sql": "${oas} - ${benchmark_oas}"
}

The ${field} syntax resolves against the model’s dimensions, measures, and other calculations.

Using Calculations in SDK Import

Calculations defined on the semantic model are automatically translated into VDM EXPRESSION measures by the BI import adapter. Once in the VDM, you can reference them in SDK import payloads using the as-is aggregation:

{
  "name": "Holdings Dashboard",
  "dataSource": { "dbId": "D2026A00002", "vdmId": "V2026A00101", "connType": "VDM" },
  "charts": [
    {
      "title": "Position % of Fund",
      "type": "column",
      "measures": [
        { "field": "pct_of_fund", "aggregation": "as-is" }
      ],
      "dimensions": ["security_name"]
    },
    {
      "title": "Weighted Avg Analytics",
      "type": "bar",
      "measures": [
        { "field": "wavg_analytics", "aggregation": "as-is" }
      ],
      "dimensions": ["sector"]
    }
  ]
}

The calculation must already exist in the VDM’s measureConfiguration before referencing it in SDK import. Define calculations on the semantic model first (via the Model Editor UI, the calculation API, or the customCalculations array during client onboarding).

How the Pipeline Works

Semantic Model (calculations defined)
    -> BI Import Adapter (translates to EXPRESSION measures)
        -> VDM (SQL baked into querySTMT)
            -> SDK Import (reference by label with "as-is")
                -> Experiment / Facade (renders in charts)

API Endpoints

Method	Endpoint	Description
`GET`	`/v1/bi/model/calculation/templates/:userId`	List all available calculation templates
`GET`	`/v1/bi/model/:modelId/calculations/:userId`	Get calculations for a model
`POST`	`/v1/bi/model/:modelId/calculation/:userId`	Add a calculation to a model
`PUT`	`/v1/bi/model/:modelId/calculation/:calcName/:userId`	Update a calculation
`DELETE`	`/v1/bi/model/:modelId/calculation/:calcName/:userId`	Delete a calculation
`POST`	`/v1/bi/model/:modelId/calculation/preview/:userId`	Preview generated SQL without saving

Preview Example

curl -X POST https://<instance>/v1/bi/model/SM2026A00001/calculation/preview/<userToken> \
  -H "Content-Type: application/json" \
  -H "CTN: <csrf-token>" \
  --cookie "LASID=<session>;ATN=<access>;RTN=<refresh>" \
  -d '{
    "calculationType": "weighted_average",
    "valueField": "duration",
    "weightField": "mkt_val",
    "partitionBy": ["acct_cd", "sector"]
  }'

Response:

{
  "modelId": "SM2026A00001",
  "calculation": {
    "calculationType": "weighted_average",
    "valueField": "duration",
    "weightField": "mkt_val",
    "partitionBy": ["acct_cd", "sector"],
    "resolvedSql": "SUM(COALESCE(duration, 0) * COALESCE(mkt_val, 0)) OVER (PARTITION BY acct_cd, sector) / NULLIF(SUM(COALESCE(mkt_val, 0)) OVER (PARTITION BY acct_cd, sector), 0)"
  }
}

Data Configuration

​Overview

​Supported Calculation Types

​Pre-built Templates

​Percentage Templates

​Weighted Average Templates

​Percentile Templates

​Calculation Object Structure

​Fields

​Weighted Average Example

​Custom SQL Example

​Using Calculations in SDK Import

​How the Pipeline Works

​API Endpoints

​Preview Example

Overview

Supported Calculation Types

Pre-built Templates

Percentage Templates

Weighted Average Templates

Percentile Templates

Calculation Object Structure

Fields

Weighted Average Example

Custom SQL Example

Using Calculations in SDK Import

How the Pipeline Works

API Endpoints

Preview Example