> ## Documentation Index
> Fetch the complete documentation index at: https://docs.tarvah.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Calculations

> Complex calculations — percentage of total, weighted averages, percentiles, and custom SQL

## 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.

<Steps>
  <Step title="Open your VDM">
    Go to **DataBridge** → **Virtual Data Marts** and open the VDM you want to configure.
  </Step>

  <Step title="Add a Calculation transform">
    In the Transformations panel, click **+** and select **Calculation**.
  </Step>

  <Step title="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.
  </Step>

  <Step title="Save the VDM">
    Click Save. The calculation is now available as a measure in any experiment using this VDM.
  </Step>
</Steps>

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

***

## 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:**

```sql theme={null}
"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:**

```sql theme={null}
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:**

```sql theme={null}
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:**

```sql theme={null}
${MKT_VAL} / NULLIF(${QTY}, 0)
```

**Resolved SQL:**

```sql theme={null}
"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:

```json theme={null}
{
  "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

<Steps>
  <Step title="Add Calculation">Click **+** in the Transformations panel → select **Calculation**.</Step>

  <Step title="Configure">
    * **Field Name:** `Wtd Avg ESG Score`
    * **Calculation Type:** Weighted Average
    * **Value Field:** `ESG_SCORE`
    * **Weight Field:** `MKT_VAL`
    * **Partition By:** `ACCT_CD`
  </Step>

  <Step title="Verify preview">
    The SQL Preview should show:

    ```sql theme={null}
    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)
    ```
  </Step>

  <Step title="Save">Click Save.</Step>
</Steps>

### Step 3: Add % of Fund

<Steps>
  <Step title="Add Calculation">Click **+** → **Calculation**.</Step>

  <Step title="Configure">
    * **Field Name:** `% of Fund`
    * **Calculation Type:** Percentage of Total
    * **Value Field:** `MKT_VAL`
    * **Partition By:** `ACCT_CD`
  </Step>

  <Step title="Save">Click Save.</Step>
</Steps>

### Step 4: Add Median Analytic Value

<Steps>
  <Step title="Add Calculation">Click **+** → **Calculation**.</Step>

  <Step title="Configure">
    * **Field Name:** `Median Analytic`
    * **Calculation Type:** Percentile
    * **Value Field:** `ANALYTIC_VAL`
    * **Percentile:** `0.5`
    * **Partition By:** `ACCT_CD`
  </Step>

  <Step title="Save">Click Save.</Step>
</Steps>

### 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:

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

<Info>
  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.
</Info>

***

## 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)
```

<Tip>
  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.
</Tip>