> For the complete documentation index, see [llms.txt](https://docs.infotopics.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.infotopics.com/trulyp/analytics/calculations.md).

# Calculations

A calculation is a field you define yourself with a formula. Use calculations for ratios such as margin percentages, for IF/THEN logic, for combining text, and for window calculations such as running totals, rankings and percentage of total.

<figure><img src="https://2312557015-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWbWxEBax8PKTjFYRLWgi%2Fuploads%2Fgit-blob-176cd43076e17adb00582bc10188903bbaf2e4fd%2FAnalytics_Calculation_Margin.png?alt=media" alt="The Create calculation dialog with a margin formula"><figcaption></figcaption></figure>

### Create a calculation

1. In the editor, click **+** next to **Fields** (**New calculation**).
2. Enter a **Field name**, for example "Gross margin %".
3. Type the **Formula**, for example `SUM(gross_margin) / SUM(net_sales) * 100`. Above the formula box you see whether the formula is valid.
4. Click **Create calculation**.

The calculation appears in the Fields list in purple, under its data source and on the **Calculations** filter. You can use it in any visual that uses the same data source, just like a measure.

<figure><img src="https://2312557015-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWbWxEBax8PKTjFYRLWgi%2Fuploads%2Fgit-blob-aaf9083cd582123f39d483a585ead40c7ac2e2fd%2FAnalytics_Fields_Calculations.png?alt=media" alt="Calculations in the Fields list" width="220"><figcaption></figcaption></figure>

To change a calculation, click its pencil icon in the Fields list. To remove it, click the trash icon. Visuals that use the calculation refresh when you save a change.

### Writing formulas

* Refer to a **measure** with an aggregation: `SUM(net_sales)`, `AVG(unit_price)`, `COUNTD(customer_id)`.
* Refer to a **dimension** or another calculation with square brackets: `[channel]`, `[Gross margin %]`.
* Use `+ - * /` and parentheses for arithmetic. `+` also joins text.
* Text values go between double quotes: `"web"`.

**Autocomplete** helps you while typing. It suggests fields, functions and other calculations; use the arrow keys and **Enter** or **Tab** to insert a suggestion, or **Esc** to close the list.

<div><figure><img src="https://2312557015-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWbWxEBax8PKTjFYRLWgi%2Fuploads%2Fgit-blob-038486f30e5fad7b111ed4e23556dfde57b4f78f%2FAnalytics_Calculation_Autocomplete.png?alt=media" alt="Autocomplete suggesting window functions"><figcaption><p>Autocomplete</p></figcaption></figure> <figure><img src="https://2312557015-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWbWxEBax8PKTjFYRLWgi%2Fuploads%2Fgit-blob-7d509351a313540fad3b012ef0d606cbdd03f58f%2FAnalytics_Calculation_Functions.png?alt=media" alt="The formula library with functions"><figcaption><p>Formula library</p></figcaption></figure></div>

The **formula library** on the right lists all **Fields** and **Functions** with a short description. Click an item to insert it into the formula.

### Functions

**Aggregations**

| Function                    | Result                    |
| --------------------------- | ------------------------- |
| `SUM(field)`                | Total                     |
| `AVG(field)`                | Average                   |
| `MIN(field)` / `MAX(field)` | Lowest / highest value    |
| `COUNT(field)`              | Number of rows            |
| `COUNTD(field)`             | Number of distinct values |

**Logic**

```
IF SUM(net_sales) > 100000 THEN "Top" ELSEIF SUM(net_sales) > 50000 THEN "Good" ELSE "Low" END
```

* `IF … THEN … ELSE … END`, with as many `ELSEIF … THEN …` branches as you need. `ELSE` is required.
* `AND`, `OR` and `NOT` combine conditions.
* `NULL`, `TRUE` and `FALSE` are available.
* The short form `condition ? value : other` also works.

**Window calculations**

Window calculations run on the results of the visual after aggregation. They let you compare a row with the other rows in the same chart.

| Function                                                                                | Result                                                                    |
| --------------------------------------------------------------------------------------- | ------------------------------------------------------------------------- |
| `WINDOW_SUM`, `WINDOW_AVG`, `WINDOW_MIN`, `WINDOW_MAX`, `WINDOW_COUNT`, `WINDOW_MEDIAN` | The sum, average and so on over all rows in the visual                    |
| `TOTAL(...)`                                                                            | The total over all rows                                                   |
| `SIZE()`                                                                                | The number of rows in the visual                                          |
| `RUNNING_SUM`, `RUNNING_AVG`, `RUNNING_MIN`, `RUNNING_MAX`, `RUNNING_COUNT`             | A running total, average and so on, from the first row to the current row |
| `RANK`, `RANK_DENSE`, `RANK_UNIQUE`                                                     | The rank of the row                                                       |
| `INDEX()`, `FIRST()`, `LAST()`                                                          | The position of the row                                                   |

Window calculations are marked with an orange **window** badge in the Fields list.

### Examples

| Calculation          | Formula                                                 |
| -------------------- | ------------------------------------------------------- |
| Gross margin %       | `SUM(gross_margin) / SUM(net_sales) * 100`              |
| Average order value  | `SUM(net_sales) / COUNTD(order_id)`                     |
| Difference to budget | `SUM(actual) - SUM(budget)`                             |
| Share of total (%)   | `SUM(net_sales) / WINDOW_SUM(SUM(net_sales)) * 100`     |
| Running total        | `RUNNING_SUM(SUM(net_sales))`                           |
| Rank by sales        | `RANK(SUM(net_sales))`                                  |
| Flag a channel       | `IF [channel] = "web" THEN "Online" ELSE "Offline" END` |
| Label with value     | `[product_name] + ": " + SUM(net_sales)`                |

<figure><img src="https://2312557015-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWbWxEBax8PKTjFYRLWgi%2Fuploads%2Fgit-blob-93f3917608975939bb9bc148cdba122c2319e94e%2FAnalytics_Calculation_Window.png?alt=media" alt="Creating a share-of-total window calculation"><figcaption><p>A window calculation for share of total</p></figcaption></figure>

{% hint style="success" %}
Use a calculation for the value, and set the number format of the visual (for example a **Suffix** of `%` and one decimal) on the **Styling** sub-tab.
{% endhint %}

### Good to know

* A calculation belongs to one data source and can only be used in visuals that use that data source.
* Calculations are always treated as measures.
* Commas are not allowed in formulas. Write `1000`, not `1,000`.
* Common error messages: **Unknown field** (check the spelling of the field name), **Unclosed parenthesis** or **Unclosed bracket**, and **Each IF must have a matching END**.
* Calculations are saved with the app.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.infotopics.com/trulyp/analytics/calculations.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.