Logical Functions
Logical functions return value based on some logical conditions.
case when
case(when: condition_expression, then: value_expression, else: value_expression)
case(when: users.gender == 'm', then: 'male') // else: null
case(
when: users.gender == 'm', then: 'male',
when: users.gender == 'f', then: 'female',
else: 'others'
)
Description
The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement).
Return type
Vary
Sample Usages
Given an AQL expression as below:
case(
when: users.gender == 'm', then: 'male',
when: users.gender == 'f', then: 'female',
else: 'others'
)
And the result would be:
| gender | case |
|---|---|
| m | male |
| f | female |
| m | male |
You can also use unicode within a CASE statement to make your metric returns emoji:
case(
when: count(orders.id) > 10
, then: '✅'
, else: cast(count(orders.id), 'text')
)
When used with the gender dimension, the above expression result in:
| gender | order_count |
|---|---|
| m | ✅ |
| f | 5 |
| m | ✅ |
and
and(condition_expression, ...)
and(products.id >= 2, products.id <= 8)
and(products.id >= 2, products.id <= 8, products.id != 5)
Description
Logical AND compares between multiple Truefalse expressions and returns true when all expressions are true.
Return type
Truefalse
Example
Given an AQL expression as below:
and(products.id >= 2, products.id <= 8)
And the result would be:
| id | and |
|---|---|
| 1 | false |
| 2 | true |
| 8 | true |
| 9 | false |
or
or(condition_expression, ...)
or(products.id <= 2, products.id >= 8)
or(products.id <= 2, products.id >= 8, products.id != 5)
Description
Logical OR compares between multiple Truefalse expressions and returns true when at least one expression is true.
Return type
Truefalse
Sample Usages
Given an AQL expression as below:
or(products.id <= 2, products.id >= 8)
And the result would be:
| id | or |
|---|---|
| 1 | true |
| 4 | false |
| 7 | false |
| 9 | true |
not
not(condition_expression)
not(products.id <= 2)
Description
Logical NOT takes a single Truefalse expression and returns true when the expression is false.
Return type
Truefalse
Sample Usages
Given an AQL expression as below:
not(is(products.id, null))
And the result would be:
| id | not |
|---|---|
| 1 | true |
| false | |
| 3 | true |
| 4 | true |
is
This function was deprecated in favor of the is operator and only kept for backward compatibility with Business Calculation. Please use the operator instead.
is(field_expression, value_expression)
is(products.id, null)
Description
Logical IS evaluates the given statement and return either true or false.
Return type
Truefalse
Sample Usages
Given an AQL expression as below:
is(products.id, null)
And the result would be:
| id | not |
|---|---|
| 1 | false |
| true | |
| 3 | false |
| 4 | false |
in
This function was deprecated in favor of the in operator and only kept for backward compatibility with Business Calculation. Please use the operator instead.
in(field_expression, value_expression, value...)
in(users.name, 'bob', 'alice', 'jack')
Description
in operator takes a field expression and a list of values. Return true if that list of values contains the value of that field expression.
Return type
Boolean
Sample Usages
Given an AQL expression as below:
in(users.name, 'bob', 'alice', 'jack')
And the result would be:
| name | in |
|---|---|
| bob | true |
| alice | true |
| peter | false |