Logical Functions

Logical functions return value based on some logical conditions.

case when

case(when: condition_expression, then: value_expression, else: value_expression)

Examples

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, ...)

Examples

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, ...)

Examples

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)

Examples

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

warning

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)

Examples

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

warning

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

Examples

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