The expressions property specifies new columns in Perspective that are
created using existing column values or arbitrary scalar values defined within
the expression. In <perspective-viewer>, expressions are added using the "New
Column" button in the side panel.
Expressions are strings parsed by Perspective's expression engine (based on
ExprTK). Column names are referenced by
wrapping them in double quotes, e.g. "Sales":
const view = await table.view({
expressions: {
"Profit Ratio": '"Profit" / "Sales"',
},
});view = table.view(expressions={'Profit Ratio': '"Profit" / "Sales"'})let view = table.view(Some(ViewConfigUpdate {
expressions: Some(Expressions([
("Profit Ratio", "\"Profit\" / \"Sales\"".into())
].into_iter().collect())),
..ViewConfigUpdate::default()
})).await?;Perspective expressions are typed: every column, literal and function result has a fixed type, and the validator reports an error before the expression is ever computed if the types do not fit the operator. To move between types explicitly, use the conversion functions:
| Function | Description |
|---|---|
to_string(x) |
Convert any type to string |
to_integer(x) |
Convert to integer (null if not parsable) |
to_float(x) |
Convert to float (null if not parsable) |
to_boolean(x) |
Convert to boolean (truthy/falsy) |
integer(x) |
Alias for to_integer(x) |
float(x) |
Alias for to_float(x) |
datetime(x) |
Construct a datetime from a POSIX timestamp (ms since epoch) |
date(y, m, d) |
Construct a date from year, month, day |
Numeric types promote to each other. Arithmetic on any mix of integer and
float operands is computed in floating point and produces a float. The
comparison operators compare values across every numeric type: integers are
compared exactly (including signed against unsigned), and as soon as one side is
a float both sides are compared as doubles. Numeric literals are float, so
"Quantity" > 3 works on an integer column without a cast.
No other implicit coercion exists. boolean, string, date and datetime
values can only be compared with values of the same type; comparing a string
column to a number, a boolean to 1, or a date to a datetime is a
validation error that names the operator and both types, for example
Type Error - cannot compare string and float with '=='. Similarly, datetime
and date values are not numeric: to perform arithmetic on them, you must first
convert to a numeric representation, do the math, then convert back.
Boolean contexts cast instead. The condition of if and ? :, and the operands
of and, or, not, xor, nand, nor and xnor, accept any type: null
is false, a boolean is its own value, a number is true when non-zero, and
a string is true when non-null. x == null and x != null test x for
null and return boolean, the same as is_null(x) and is_not_null(x); the
null literal is otherwise a value like any null cell: "x" > 2 ? null : "x"
yields null in the first case, and "x" + null or "x" < null are null, exactly
as they would be for a column with a null value.
Internally, datetime values are stored as milliseconds since the Unix epoch
(1970-01-01T00:00:00Z). Converting a datetime to a float yields this
millisecond timestamp, and datetime() accepts a millisecond timestamp to
produce a datetime.
This expression takes a "Shipped Date" column, converts it to its
millisecond-epoch representation, adds 7 days worth of milliseconds (7 ×
24 × 60 × 60 × 1000 = 604800000), and converts the result back
to a datetime:
// Due Date
datetime(float("Shipped Date") + 604800000)
Standard arithmetic and comparison operators are supported:
| Operator | Description |
|---|---|
+, -, *, / |
Arithmetic |
% |
Modulo |
==, !=, <, >, <=, >= |
Comparison |
and, or, not |
Logical |
if ... else ... |
Conditional |
ExprTK provides a rich set of built-in numeric functions including abs,
ceil, floor, round, exp, log, log10, sqrt, min, max, pow,
clamp, iclamp, inrange, and trigonometric functions (sin, cos, tan,
asin, acos, atan).
| Function | Description |
|---|---|
concat(a, b, ...) |
Concatenate strings |
upper(s) |
Convert to uppercase |
lower(s) |
Convert to lowercase |
length(s) |
String length |
contains(s, substr) |
Whether s contains substr |
order(col, 'B', 'C', 'A') |
Custom sort order for a string column |
match(s, pattern) |
Regex partial match (returns boolean) |
match_all(s, pattern) |
Regex full match (returns boolean) |
search(s, pattern) |
First capturing group match |
indexof(s, pattern) |
Start index of first regex match |
substring(s, start, end) |
Substring from start (inclusive) to end (exclusive) |
replace(s, repl, pattern) |
Replace first regex match |
replace_all(s, repl, pattern) |
Replace all regex matches |
| Function | Description |
|---|---|
today() |
Current date |
now() |
Current datetime |
date(year, month, day) |
Construct a date |
datetime(timestamp_ms) |
Construct a datetime from a POSIX timestamp (ms since epoch) |
hour_of_day(dt) |
Hour component (0-23) |
day_of_week(dt) |
Day of the week as a string |
month_of_year(dt) |
Month of the year as a string |
bucket(dt, unit) |
Bucket datetime by unit: 's', 'm', 'h', 'D', 'W', 'M', 'Y' |
bucket also works on numeric columns: bucket("Price", 10) rounds values down
to the nearest multiple of 10.
| Function | Description |
|---|---|
is_null(x) |
Whether the value is null |
is_not_null(x) |
Whether the value is not null |
percent_of(a, b) |
a as a percentage of b |
inrange(low, val, high) |
Whether val is between low and high (inclusive) |
min(a, b, ...) |
Minimum of inputs |
max(a, b, ...) |
Maximum of inputs |
random() |
Random float between 0.0 and 1.0 |
col(name) |
Look up a column by string name at runtime |
vlookup(col, key) |
Look up a value in another column by row key |
Expressions are row-local — each output cell is computed from that row's values alone. For calculations which span rows, such as moving averages, cumulative sums or period-over-period differences, see Window Columns.