Expressions & Formulas

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

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

8.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.
Column autocomplete — Suggests column names as you type.
Real-time validation — Shows errors immediately.

8.3 Referencing Columns

Use the column name directly in your expression:

PRICE * QUANTITY
FIRST_NAME

8.4 Cross-Sheet References

To reference data from another sheet, use the #SheetName syntax:


COUNT(#ORDERS)

SUM(#ORDERS.AMOUNT)

AVERAGE(#ORDERS['CUSTOMER_REF'].AMOUNT)

SUM(#INVOICES#LINE_ITEMS.TOTAL)

8.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, STATUS = "Completed")

Date Functions

Function	Description	Example
`TODAY()`	Returns today’s date	`TODAY()`
`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")`

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

8.6 Operators

Category	Operators	Example
Arithmetic	`+` `-` `*` `/`	`PRICE * QUANTITY`
Comparison	`=` `<>` `>` `<` `>=` `<=`	`AGE >= 18`
Logical	`AND` `OR` `NOT`	`ACTIVE = TRUE AND ROLE = "Admin"`

8.7 Practical Examples

Virtual Column: Order Total

PRICE * QUANTITY

Virtual Column: Days Since Creation

DATEDIF(CREATED_DATE, TODAY(), "D")

Virtual Column: Number of Related Orders

COUNT(#ORDERS)

Virtual Column: Total Revenue from Completed Orders

SUM(#ORDERS.AMOUNT, STATUS = "Completed")

Virtual Column: Status Label

IF(BALANCE > 0, "Active", "Inactive")

View Filter: Active Items This Year

STATUS = "Active" AND YEAR(CREATED_DATE) = YEAR(TODAY())

Next: Import →