> For the complete documentation index, see [llms.txt](https://www.analytics-docs.laplacetec.com/guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.analytics-docs.laplacetec.com/guide/en/customdashboard/custom/custom_formula.md).

# Custom formula

{% hint style="info" %}
What is a custom formula?

A custom formula is a feature that **lets you build your own calculations** when analyzing data in Laplace. Much like formulas in Excel or Google Sheets, you can combine existing measures and dimensions to create new metrics.
{% endhint %}

#### What you can do with custom formulas

1. Ratio calculations: Key metrics such as conversion rate, average order value and cost per click
2. Goal management: Goal attainment rate, actual vs. target, progress tracking
3. Conditional analysis: Grading and segmentation
4. String handling: Cleaning, parsing, joining and converting data
5. Date operations: Extracting year · month · day · week number · weekday · quarter
6. Compound calculations: Advanced metrics such as ROAS, ROI and net profit margin

## Create a custom formula <a href="#create" id="create"></a>

1. On the **Data** tab of the settings panel on the right of the chart editor, choose a data source and measures first.
2. Under **Data** tab › **Custom formula**, click **New formula**.
3. In **Formula name**, enter the name to show in the table and the measure list. (e.g. ROAS)
4. Enter measures and functions in **Expression**. Hover over a function or **Example** in the list on the left to see a description; click it to insert it into the expression.
5. Under **Number format**, choose one of **Number** · **Currency** · **Ratio** · **Text**, then click **Save**.

   ![Custom formula editor](https://616205588-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fqr1veGyUqCNwQBBmk3yt%2Fuploads%2Fgit-blob-24b695f6cf8b105e31fbce8f9eb53a3ddd08912e%2Fcustomdashboard__custom__custom_formula__01.png?alt=media)

{% hint style="info" %}
Saved formulas are added to the table automatically and also appear in the measure list on the **Chart** tab, so you can chart them. You can **Edit formula** or **Duplicate** a formula from the **Custom formula** list.
{% endhint %}

## How it works <a href="#working_principle" id="working_principle"></a>

Custom formulas work in the following order:

1. Enter an expression

{% hint style="success" %}
e.g. DIVIDE('revenue', 'order\_count')
{% endhint %}

2. Syntax check\
   Checks parentheses, quotes and function names, and whether the referenced columns exist in the measures and dimensions currently selected
3. Calculation\
   Queries data with the selected measures and dimensions, then applies the expression to each row of the result to create a new column
4. Display results\
   Shows the calculated values on the dashboard

## Comparison with Excel <a href="#excel_comparison" id="excel_comparison"></a>

<table><thead><tr><th width="135">Item</th><th>Excel/spreadsheets</th><th>Laplace custom formula</th></tr></thead><tbody><tr><td>Cell reference</td><td>A1, B2:B10</td><td>'revenue', 'order_count'</td></tr><tr><td>Division</td><td>=A1/B1</td><td>DIVIDE('revenue', 'order_count')</td></tr><tr><td>Condition</td><td>=IF(A1>100, "High", "Low")</td><td>IF('revenue' > 100, "High", "Low")</td></tr><tr><td>Text</td><td>="Hello " &#x26; A1</td><td>CONCAT("Hello ", 'product_name')</td></tr></tbody></table>

{% hint style="info" %}
Key differences

* Wrap column names in **single quotes (\`'\`)**: 'revenue'
* Wrap string values in **double quotes (\`"\`)**: "Completed"
* Function names aren't case-sensitive. This document writes them in uppercase: DIVIDE
  {% endhint %}

## Basic syntax <a href="#basic_syntax" id="basic_syntax"></a>

### 1. Column references <a href="#column_reference" id="column_reference"></a>

'column\_name'

```
Examples
'revenue' - Revenue
'order_count' - Number of orders
'product_name' - Product name
```

{% hint style="info" %}
The column names in this document (`'revenue'`, `'order_count'`, etc.) are examples to illustrate the syntax. In a real expression, pick a measure or dimension name from the input's autocomplete and that column's key is inserted wrapped in single quotes. For example, revenue is inserted as `'sales'`, and when you use several data sources, a source letter is added in front, like `'A__sales'`.
{% endhint %}

### 2. Number literals <a href="#numeric_literals" id="numeric_literals"></a>

```
100        # integer
3.14       # decimal
0 - 50     # negative number
```

You can't write a negative number directly as `-50`. Make it with subtraction, like `0 - 50`.

### 3. String literals <a href="#string_literals" id="string_literals"></a>

Use double quotes.

```
"Text"
"Hello World"
"Completed"
```

### 4. Operator precedence <a href="#operator_precedence" id="operator_precedence"></a>

1. **() Parentheses**\
   Calculated first.
2. **\* / Multiplication, division**

   Calculated before addition/subtraction
3. **+ - Addition, subtraction**

   Calculated after multiplication/division
4. **> < >= <= Comparison operators**

   Compared after arithmetic

## Operators <a href="#operator" id="operator"></a>

### Arithmetic operators <a href="#arithmetic_operators" id="arithmetic_operators"></a>

<table><thead><tr><th width="85">Operator</th><th width="117">Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td>+</td><td>Add</td><td>'revenue' + 'cost'</td><td>Revenue + cost</td></tr><tr><td>-</td><td>Subtract</td><td>'revenue' - 'cost'</td><td>Net profit</td></tr><tr><td>*</td><td>Multiply</td><td>'quantity' * 'unit_price'</td><td>Total amount</td></tr><tr><td>/</td><td>Divide</td><td>'revenue' / 'order_count'</td><td>Average order value</td></tr></tbody></table>

### Comparison operators <a href="#comparison_operators" id="comparison_operators"></a>

<table><thead><tr><th width="85">Operator</th><th width="117">Description</th><th>Example</th></tr></thead><tbody><tr><td>=</td><td>Equal to</td><td>'status' = "Completed"</td></tr><tr><td>&#x3C;></td><td>Not equal to</td><td>'status' &#x3C;> "Canceled"</td></tr><tr><td>></td><td>Greater than</td><td>'revenue' > 1000</td></tr><tr><td>&#x3C;</td><td>Less than</td><td>'revenue' &#x3C; 1000</td></tr><tr><td>>=</td><td>Greater than or equal to</td><td>'order_count' >= 2</td></tr><tr><td>&#x3C;=</td><td>Less than or equal to</td><td>'order_count' &#x3C;= 1</td></tr></tbody></table>

### Logical functions <a href="#logical_operators" id="logical_operators"></a>

`AND` and `OR` are used as functions, not as operators placed between conditions. Writing `'revenue' > 1000 AND 'status' = "Completed"` causes an error.

<table><thead><tr><th width="114">Function</th><th width="189">Description</th><th>Example</th></tr></thead><tbody><tr><td>AND</td><td>AND(condition1, condition2): both are true</td><td>AND('revenue' > 1000, 'status' = "Completed")</td></tr><tr><td>OR</td><td>OR(condition1, condition2): at least one is true</td><td>OR('category' = "Apparel", 'category' = "Accessories")</td></tr><tr><td>NOT</td><td>NOT(condition): flips true and false</td><td>NOT('revenue' > 1000)</td></tr></tbody></table>

You can include up to two conditions. For three or more, nest them like `AND(condition1, AND(condition2, condition3))`.

## Function types <a href="#function_types" id="function_types"></a>

{% hint style="info" %}
An expression is applied separately to each row of the query result. There are no aggregate functions that combine multiple rows, such as sum · average · count · min · max.
{% endhint %}

### Math functions <a href="#acceptance_functions" id="acceptance_functions"></a>

<table><thead><tr><th width="126">Function</th><th>Syntax</th><th width="117">Description</th><th>Example</th></tr></thead><tbody><tr><td>MULTIPLY</td><td>MULTIPLY(a, b)</td><td>Multiplication</td><td>MULTIPLY('quantity', 'unit_price')</td></tr><tr><td>DIVIDE</td><td>DIVIDE(a, b)</td><td>Division</td><td>DIVIDE('revenue', 'order_count')</td></tr><tr><td>SUBTRACT</td><td>SUBTRACT(a, b)</td><td>Subtraction</td><td>SUBTRACT('revenue', 'cost')</td></tr><tr><td>ABS</td><td>ABS(value)</td><td>Absolute value</td><td>ABS('profit_loss')</td></tr><tr><td>FLOOR</td><td>FLOOR(value)</td><td>Round down</td><td>FLOOR('decimal_value')</td></tr><tr><td>CEIL</td><td>CEIL(value)</td><td>Round up</td><td>CEIL('decimal_value')</td></tr></tbody></table>

### Conditional functions <a href="#conditional_functions" id="conditional_functions"></a>

<table><thead><tr><th width="128">Function</th><th>Syntax</th><th width="115">Description</th><th>Example</th></tr></thead><tbody><tr><td>IF</td><td>IF(condition, true_value, false_value)</td><td>Condition</td><td>IF('revenue' > 1000, "Excellent", "Average")</td></tr><tr><td>IFS</td><td>IFS(cond1, val1, cond2, val2, ...)</td><td>Multiple conditions. Rows that match no condition are blank</td><td>IFS('revenue' >= 10000, "Top", 'revenue' >= 5000, "Excellent")</td></tr><tr><td>COALESCE</td><td>COALESCE(val1, val2, ...)</td><td>Replace NULL</td><td>COALESCE('discount', 0)</td></tr><tr><td>NULLIF</td><td>NULLIF(val1, val2)</td><td>NULL if equal</td><td>NULLIF('order_count', 0)</td></tr></tbody></table>

### String functions <a href="#string_functions" id="string_functions"></a>

<table><thead><tr><th width="109">Function</th><th>Syntax</th><th width="116">Description</th><th>Example</th></tr></thead><tbody><tr><td>CONCAT</td><td>CONCAT(str1, str2, ...)</td><td>Join strings</td><td>CONCAT('first_name', " ", 'last_name')</td></tr><tr><td>UPPER</td><td>UPPER(text)</td><td>Convert to uppercase</td><td>UPPER('product_code')</td></tr><tr><td>LOWER</td><td>LOWER(text)</td><td>Convert to lowercase</td><td>LOWER('email')</td></tr><tr><td>TRIM</td><td>TRIM(text)</td><td>Remove spaces</td><td>TRIM('address')</td></tr><tr><td>LENGTH</td><td>LENGTH(text)</td><td>String length</td><td>LENGTH('product_name')</td></tr><tr><td>SUBSTR</td><td>SUBSTR(text, start, length)</td><td>Substring</td><td>SUBSTR('phone', 1, 3)</td></tr><tr><td>REPLACE</td><td>REPLACE(text, old, new)</td><td>Replace text</td><td>REPLACE('address', "St.", "Street")</td></tr></tbody></table>

### Date/time functions <a href="#datetime_functions" id="datetime_functions"></a>

<table><thead><tr><th width="157">Function</th><th>Syntax</th><th width="111">Description</th><th>Example</th></tr></thead><tbody><tr><td>NOW</td><td>NOW()</td><td>Current time</td><td>NOW()</td></tr><tr><td>CURRENT_DATE</td><td>CURRENT_DATE()</td><td>Today's date</td><td>CURRENT_DATE()</td></tr><tr><td>DATE</td><td>DATE(year, month, day)</td><td>Create a date</td><td>DATE(2024, 1, 15)</td></tr><tr><td>YEAR</td><td>YEAR(date)</td><td>Extract year</td><td>YEAR('time_id')</td></tr><tr><td>MONTH</td><td>MONTH(date)</td><td>Extract month</td><td>MONTH('time_id')</td></tr><tr><td>DAY</td><td>DAY(date)</td><td>Extract day</td><td>DAY('time_id')</td></tr><tr><td>WEEK</td><td>WEEK(date)</td><td>Extract week number</td><td>WEEK('time_id')</td></tr><tr><td>DAY_OF_WEEK</td><td>DAY_OF_WEEK(date)</td><td>Weekday (1=Sun, 2=Mon, ..., 7=Sat)</td><td>DAY_OF_WEEK('time_id')</td></tr><tr><td>QUARTER</td><td>QUARTER(date)</td><td>Quarter</td><td>QUARTER('time_id')</td></tr></tbody></table>

### Regex functions <a href="#regex_functions" id="regex_functions"></a>

<table><thead><tr><th>Function</th><th>Syntax</th><th width="123">Description</th><th>Example</th></tr></thead><tbody><tr><td>REGEXP_EXTRACT</td><td>REGEXP_EXTRACT(text, pattern)</td><td>Extract with regex</td><td>REGEXP_EXTRACT('url', "utm_source=([^&#x26;]+)")</td></tr><tr><td>REGEXP_REPLACE</td><td>REGEXP_REPLACE(text, pattern, replacement)</td><td>Replace with regex</td><td>REGEXP_REPLACE('phone', "[^0-9]", "")</td></tr></tbody></table>

### Type conversion functions <a href="#type_conversion_functions" id="type_conversion_functions"></a>

<table><thead><tr><th width="93">Function</th><th>Syntax</th><th width="124">Description</th><th>Example</th></tr></thead><tbody><tr><td>CAST</td><td>CAST(value, "TYPE")</td><td>Type conversion</td><td>CAST('amount', "DOUBLE")</td></tr></tbody></table>

{% hint style="warning" %}
`VARCHAR`, `DOUBLE`, `BIGINT`, `BOOLEAN`, `DATE`, `TIMESTAMP`
{% endhint %}

## Examples <a href="#examples" id="examples"></a>

### Ratio calculations <a href="#ratio_calculation" id="ratio_calculation"></a>

<table><thead><tr><th width="141">Item</th><th width="183">Calculation</th><th width="296">Syntax</th><th>Result</th></tr></thead><tbody><tr><td>Conversion rate (CVR)</td><td>(Orders ÷ clicks) × 100</td><td>DIVIDE('order_count' * 100, 'clicks')</td><td>5.0%</td></tr><tr><td>Average order value (AOV)</td><td>Revenue ÷ orders</td><td>DIVIDE('revenue', 'order_count')</td><td>₩ 50,000</td></tr><tr><td>Cost per click (CPC)</td><td>Ad spend ÷ clicks</td><td>DIVIDE('ad_cost', 'clicks')</td><td>₩ 200</td></tr><tr><td>ROAS</td><td>(Revenue ÷ ad spend) × 100</td><td>DIVIDE('revenue' * 100, 'ad_cost')</td><td>500%</td></tr></tbody></table>

### Goal management <a href="#goal_management" id="goal_management"></a>

<table><thead><tr><th width="141">Item</th><th width="183">Calculation</th><th width="296">Syntax</th><th>Result</th></tr></thead><tbody><tr><td>Goal attainment rate</td><td>(Actual revenue ÷ target revenue) × 100</td><td>DIVIDE('revenue' * 100, 'target_revenue')</td><td>85%</td></tr><tr><td>Goal attainment grade</td><td>S (130%↑) / A (110%↑) / B (90%↑) / C (90%↓)</td><td>IF(DIVIDE('revenue', 'target_revenue') >= 1.3, "Grade S",<br>IF(DIVIDE('revenue', 'target_revenue') >= 1.1, "Grade A",<br>IF(DIVIDE('revenue', 'target_revenue') >= 0.9, "Grade B",<br>"Grade C")))</td><td>Grade A</td></tr><tr><td>Difference from target</td><td>Actual revenue - target revenue</td><td>SUBTRACT('revenue', 'target_revenue')</td><td>+ ₩ 2,000,000</td></tr><tr><td>Required daily revenue</td><td>(Target - current) ÷ days remaining</td><td>DIVIDE( SUBTRACT('target_revenue', 'current_revenue'), 'days_remaining' )</td><td>₩ 1,200,000/day</td></tr></tbody></table>

### Customer segmentation <a href="#customer_segmentation" id="customer_segmentation"></a>

<table><thead><tr><th width="141">Item</th><th width="183">Calculation</th><th width="296">Syntax</th><th>Result</th></tr></thead><tbody><tr><td>Customer tiers</td><td>Platinum (50K↑) / Gold (20K↑) / Silver (10K↑) / Bronze</td><td>IF('revenue' >= 50000, "Platinum",<br>IF('revenue' >= 20000, "Gold",<br>IF('revenue' >= 10000, "Silver",<br>"Bronze")))</td><td></td></tr><tr><td>Weekend/weekday</td><td>Identify weekends by weekday number</td><td>IF(OR(DAY_OF_WEEK('time_id') = 1, DAY_OF_WEEK('time_id') = 7), "Weekend", "Weekday")</td><td></td></tr><tr><td>New/returning customers</td><td>Classify customer type by number of orders</td><td>IF('order_count' > 1, "Returning", "New")</td><td></td></tr><tr><td>Age group</td><td>35 → "30s"</td><td>CONCAT(<br>CAST(FLOOR('age' / 10) * 10, "VARCHAR"),<br>"s"<br>)</td><td></td></tr></tbody></table>

## Tips and cautions <a href="#tips_cautions" id="tips_cautions"></a>

### Recommendations <a href="#recommendations" id="recommendations"></a>

{% hint style="success" %}

1. Make precedence clear with parentheses

Use parentheses to make the intent of complex expressions clear.

```
❌ 'a' + 'b' * 'c' / 'd'
✅ 'a' + (('b' * 'c') / 'd')
```

{% endhint %}

{% hint style="success" %}
2\. Division by zero is handled automatically

`DIVIDE` and `/` return a blank instead of an error when the denominator is 0. You don't need a separate zero check.

However, if you multiply or add to the result of a division, the blank turns into 0. When you need to multiply by a factor, as with percentages, multiply the numerator first.

```
DIVIDE('revenue', 'order_count')         (blank for rows with 0 orders)
DIVIDE('order_count' * 100, 'clicks')    (blank for rows with 0 clicks)
DIVIDE('order_count', 'clicks') * 100    (0 for rows with 0 clicks)
```

{% endhint %}

{% hint style="success" %}
3\. Make sure types match

Compare and calculate values of the same type.

```
❌ 'revenue' > "1000"   (number vs string)
✅ 'revenue' > 1000     (number vs number)
```

{% endhint %}

### Cautions <a href="#warnings" id="warnings"></a>

| Item             | ❌ Wrong              | ✅ Correct   |
| ---------------- | -------------------- | ----------- |
| Column reference | revenue or "revenue" | 'revenue'   |
| String           | 'Completed'          | "Completed" |
| Column name typo | 'revenu'             | 'revenue'   |

{% hint style="danger" %}
**No circular references**

An expression that references itself causes an error. You can reference other expressions, but avoid circular references.
{% endhint %}

## Frequently asked questions (FAQ)

<details>

<summary>Q. My expression doesn't work. What should I do?</summary>

**Checklist:**

1. Did you use only the functions in this document's function tables?
2. Did you wrap column names in single quotes? (`'revenue'`)
3. Did you wrap strings in double quotes? (`"Completed"`)
4. Are all parentheses closed properly?

</details>

<details>

<summary>Q. Can I reference another expression?</summary>

**Yes, you can!**\
\
First create an "Average order value" formula:\
`DIVIDE('revenue', 'order_count')`\
\
Then reference it in another formula:\
`IF('Average order value' > 50000, "VIP", "Regular")`\
\
⚠️ **Note:** Circular references aren't allowed.

</details>

<details>

<summary>Q. How can I improve formula performance?</summary>

**Recommendations:**

1. Keep complex regular expressions to a minimum.
2. Filter to only the data you need.

</details>

## Ask your dedicated manager <a href="#contact" id="contact"></a>

Having trouble using custom formulas?

Contact us through the inquiry page or by email below. Your manager will be happy to help.

> 💬 [Contact us on our website](https://www.analytics.laplacetec.com/contact)

**Email:** [**product@laplacetec.com**](mailto:product@laplacetec.com)