← 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.
- Column autocomplete — Suggests column names as you type.
- Real-time validation — Shows errors immediately.
7.3 Referencing Columns
Use the column name directly in your expression:
PRICE * QUANTITY |
FIRST_NAME |
7.4 Cross-Sheet References
To reference data from another sheet, use the #SheetName syntax:
COUNT(#ORDERS) |
SUM(#ORDERS.AMOUNT) |
SUM(#INVOICES#LINE_ITEMS.TOTAL) |
Tip: If 2 or more references exist between sheets, add the column name like this:
AVERAGE(#ORDERS['CUSTOMER_REF'].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 →