← Back to User Manual
7. Expressions & Formulas
X-Sheets includes a powerful expression language used in virtual columns (calculated fields) and view filter conditions. This chapter is a complete reference for power users.
7.1 Where Expressions Are Used
- Virtual columns — Define a formula that computes values from other columns. Added via the Data Editor’s virtual column settings.
- Filter conditions — Control which rows appear in a view. Configured in the View Editor.
7.2 The Expression Builder
Both use cases provide a visual Expression Builder with:
- A text field for typing expressions.
- Insert Function — Opens a categorized list of all functions with descriptions and examples.
- Insert Operator — Lists comparison, logical, and arithmetic operators.
- Autocomplete — Suggests columns and linked sheets by their labels as you type.
- Real-time validation — Shows errors immediately.
7.3 Referencing Columns
Reference a column by its label (the name displayed in the app), wrapped in braces:
{Price} * {Quantity} |
{First name} |
- Autocomplete does it for you — pick a suggestion and the braced label is inserted; you rarely need to type the braces yourself.
- Duplicate labels — if two columns share the same label, the editor disambiguates them as
{Label (NAME)}, where NAME is the column’s internal name.
- Rename-safe — expressions are stored using stable internal names, so renaming a column’s label never breaks your formulas; the editor simply shows the new label.
- Internal names still work — typing the internal name (e.g.
FIRST_NAME) is still accepted.
7.4 Cross-Sheet References
To reference data from another sheet, use the #{Sheet label} syntax:
COUNT(#{Orders}) |
SUM(#{Orders}.{Amount}) |
SUM(#{Invoices}#{Line items}.{Total}) |
Tip: If 2 or more references exist between sheets, add the reference column’s label like this:
AVERAGE(#{Orders}['Customer'].{Amount}) |
7.5 Function Reference
Aggregation Functions
These functions operate on collections of rows (typically from cross-sheet references):
| Function |
Description |
Example |
COUNT(collection, [condition]) |
Counts the number of rows |
COUNT(#{Orders}) |
COUNTDISTINCT(collection, [condition]) |
Counts unique values |
COUNTDISTINCT(#{Orders}.{Category}) |
SUM(collection, [condition]) |
Sums numeric values |
SUM(#{Orders}.{Amount}) |
AVERAGE(collection, [condition]) |
Calculates the average |
AVERAGE(#{Orders}.{Amount}) |
MIN(collection, [condition]) |
Finds the minimum value |
MIN(#{Scores}.{Value}) |
MAX(collection, [condition]) |
Finds the maximum value |
MAX(#{Scores}.{Value}) |
Tip: All aggregation functions accept an optional second parameter for filtering: COUNT(#{Orders}, #{Orders}.{Status} = "Completed")
Date Functions
| Function |
Description |
Example |
TODAY() |
Returns today’s date (no time) |
TODAY() |
NOW() |
Returns the current date and time (to the minute) |
NOW() |
DATE(year, month, day) |
Creates a specific date |
DATE(2024, 6, 15) |
DAY(date) |
Extracts the day (1–31) |
DAY({Birth date}) |
MONTH(date) |
Extracts the month (1–12) |
MONTH({Order date}) |
YEAR(date) |
Extracts the year |
YEAR({Hire date}) |
DATEDIF(start, end, unit) |
Calculates the difference between two dates. Units: "Y" (years), "M" (months), "D" (days) |
DATEDIF({Start date}, TODAY(), "D") |
Conditional Functions
| Function |
Description |
Example |
IF(condition, true_val, false_val) |
Returns one of two values based on a condition |
IF({Amount} > 100, "High", "Low") |
HASVALUE(column) |
Returns TRUE if the column has a valid, non-empty value |
HASVALUE({Birth date}) |
Math Functions
| Function |
Description |
Example |
INT(number) |
Converts to integer (truncates decimals) |
INT({Price} * 1.2) |
ABS(number) |
Returns the absolute value |
ABS({Balance}) |
Boolean Constants
| Constant |
Usage |
TRUE or TRUE() |
Boolean true value |
FALSE or FALSE() |
Boolean false value |
7.6 Operators
| Category |
Operators |
Example |
| Arithmetic |
+ - * / |
{Price} * {Quantity} |
| Comparison |
= <> > < >= <= |
{Age} >= 18 |
| Logical |
AND OR NOT |
{Active} = TRUE AND {Role} = "Admin" |
Date arithmetic. The + and - operators work on date values too:
| Expression |
Result |
DATE + number |
Date shifted by that many days |
DATE - number |
Date shifted backwards by that many days |
DATE - DATE |
Difference between two dates, in days (fractional) |
Tip — adding minutes: The number is always interpreted in days. To add minutes, divide by 1440 (the number of minutes in a day):
{Visit date} + ({Duration min} / 1440) |
NOW() + (30 / 1440) — 30 minutes from now |
Use / 24 for hours and / 86400 for seconds.
7.7 Practical Examples
| Use Case |
Expression |
| Order Total |
{Price} * {Quantity} |
| Days Since Creation |
DATEDIF({Created date}, TODAY(), "D") |
| Number of Related Orders |
COUNT(#{Orders}) |
| Total Revenue (Completed) |
SUM(#{Orders}.{Amount}, #{Orders}.{Status} = "Completed") |
| Status Label |
IF({Balance} > 0, "Active", "Inactive") |
| Active Items This Year |
{Status} = "Active" AND YEAR({Created date}) = YEAR(TODAY()) |
| Rows With Valid Amount |
HASVALUE({Amount}) |
Next: Import & Export →