Logical Functions
Logical functions allow you to apply conditional logic, handle null values, and perform comparisons in your formulas.
Between
Checks whether a value lies within a given range. The function is inclusive, so it includes the beginning and end values of the range.
Inclusive Range
Both the lower and upper bounds are included in the range. Between(5, 1, 10) returns True because 5 is within the range [1, 10].
Category: logical
Syntax:
Between([value_column], lower_bound, upper_bound)
Returns: BooleanColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
value |
number\|date\|datetime\|column |
✓ Yes | The input value or column containing the values to check. |
lower bound |
number\|date\|datetime |
✓ Yes | The lower bound of the range to check. |
upper bound |
number\|date\|datetime |
✓ Yes | The upper bound of the range to check. |
Allowed Column Types: INT, FLOAT, DECIMAL, DATE, DATETIME
Validation
- Minimum parameters: 3
- Maximum parameters: 3
Examples
Between([Invoice Date], MakeDate(2015, 1, 1), MakeDate(2020, 1, 1))
Returns True if a date value is between January 1, 2015 and January 1, 2020.
Between([Sale Date], DateAdd("day", -7, Now()), Now())
Returns True if a sale happened in the last 7 days.
Related Functions
Choose
Given a specified index number, returns the matching value from a list.
Category: logical
Syntax:
Choose([index_column], value1, value2, ...)
Returns: AnyColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
index |
column\|integer |
✓ Yes | The 1-based index position of the value to return. |
value1 |
any |
✓ Yes | The first value in the list. |
additional values |
variadic |
✗ No | Additional values in the list. |
Validation
- Minimum parameters: 2
- Maximum parameters: Unlimited
Examples
Choose(2, "First", "Second", "Third")
Returns the second value from the list.
Result: Second
Coalesce
Returns the value of the first argument that is not Null. Often applied to columns to check for Null values and assign placeholder values in replacement of Nulls.
Best Practice
Use Coalesce to provide default values for missing data. This prevents blank cells in reports and avoids calculation errors.
Category: logical
Syntax:
Coalesce([column_name], ...)
Returns: AnyColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
column_name |
column |
✓ Yes | The number, date, string, column, or function to be evaluated. |
additional arguments |
variadic |
✗ No | Additional arguments to be evaluated. All arguments must be of the same data type. |
Validation
- Minimum parameters: 1
- Maximum parameters: Unlimited
Examples
Coalesce(Null, 1/0, 1/1, 1/2)
Returns the third argument (1) as it is the first non-Null value.
Result: 1
Coalesce([Sales], 0)
Converts all the Null values in Sales to 0.
Coalesce([Product Type], [Product Family], [Sku Number])
Return Product Type if it exists; return Product Family if there is no Product Type. If both don't exist, return SKU Number.
Related Functions
- IsNull - Check if a value is null
- Zn - Convert nulls to zero specifically
- If - Conditional logic for more complex null handling
If
Returns Value 1 for every row where the first condition is met. If more than one condition is supplied, subsequent conditions are evaluated and return their corresponding values. If all conditions are False, then the Else value is applied.
Category: logical
Syntax:
If(condition1, value1, [condition2], [value2], ..., [else])
Returns: AnyColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
condition 1 |
logical |
✓ Yes | Logical condition that returns a result that is either True or False. |
value 1 |
any |
✓ Yes | The value to be returned if its preceding condition is True. |
additional conditions and values |
variadic |
✗ No | Several If/Then pairs can be listed in a single function. Every supplied condition must have a corresponding value. |
else |
any |
✗ No | The value to be returned if no conditionals evaluate to True. If not specified, a Null result is returned by default. |
Validation
- Minimum parameters: 2
- Maximum parameters: Unlimited
Examples
If([size] < 3, "small", [size] < 6, "medium", "large")
Assign "small" to sizes less than 3, "medium" to sizes less than 6, and "large" to all other sizes.
If([revenue] - [cost] > 0, "profit", "loss")
Categorize a record as a profit or a loss based on revenue and cost.
Nested If Statements
For complex multi-condition logic, consider using Switch instead of deeply nested If statements for better readability.
Related Functions
- Switch - Better for matching specific values
- Between - Check if value is in a range
- In - Check if value matches any in a list
In
Tests if a value matches any candidate value in a subsequent list of candidate values. Returns True if the value matches any of the subsequent candidate values, False if no values are matched.
Category: logical
Syntax:
In([value_column], candidate1, [candidates 2+])
Returns: BooleanColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
value |
column\|any |
✓ Yes | The value to test. |
candidate 1 |
any |
✓ Yes | The first candidate to test value against. |
additional candidates |
variadic |
✗ No | Additional candidates to test value against. |
Validation
- Minimum parameters: 2
- Maximum parameters: Unlimited
Examples
In("green", "red", "green", "blue")
Tests if "green" matches any of the candidate values.
Result: true
In([Customer ID], 2000, 3000, 4000)
Returns True for rows where Customer ID is 2000, 3000, or 4000.
IsNotNull
Returns True if the argument is not Null (contains data), and False if the argument contains missing data values.
Category: logical
Syntax:
IsNotNull(argument)
Returns: BooleanColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
argument |
column\|any |
✓ Yes | Any argument can be provided. The function can analyze one argument at a time. |
Validation
- Minimum parameters: 1
- Maximum parameters: 1
Examples
IsNotNull([Sales])
Returns True for rows where sales data exists and False for rows where there is no data.
IsNull
Returns True if the argument contains missing data values, and False if the argument is not Null. Often applied to a column of data to test for Null values.
Category: logical
Syntax:
IsNull(argument)
Returns: BooleanColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
argument |
column\|any |
✓ Yes | Any argument can be provided. The function can analyze one argument at a time. |
Validation
- Minimum parameters: 1
- Maximum parameters: 1
Examples
IsNull([Sales])
Returns False for rows where sales data exists and True for rows where there is no data.
IsNull([prm-date-range].start) and IsNull([prm-date-range].end)
Returns True when no start and end date is provided in a Date Range parameter.
Switch
Returns the result corresponding to the first matching value. If the case argument evaluates to True, then the corresponding result is returned, otherwise the else argument is returned.
Category: logical
Syntax:
Switch(value, case1, result1, [case2], [result2], ..., [else])
Returns: AnyColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
value |
column\|any |
✓ Yes | The value to test. |
case 1 |
any |
✓ Yes | The case to test the value against. |
result 1 |
any |
✓ Yes | The result to be returned if its preceding case matches the input value. |
additional cases and results |
variadic |
✗ No | Several pairs can be listed in a single function. Every supplied case must have a corresponding result. |
else |
any |
✗ No | The result to be returned if no cases match the value. If not supplied, a Null result is returned. |
Validation
- Minimum parameters: 3
- Maximum parameters: Unlimited
Examples
Switch(1, 0, "None", 1, "One", "Many")
Tests value 1 against cases and returns corresponding result.
Result: One
Switch([dimension-param], "Product Family", [Product Family], "Product Type", [Product Type])
Dynamically returns different columns based on parameter selection.
Zn
Returns non-Null values, or 0 (zero) instead of Null values.
Category: logical
Syntax:
Zn([value_column])
Returns: NumberColumn
Context Filtering: ✓ Yes
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
value_column |
number\|column |
✓ Yes | The value or column to evaluate. Null values are converted to 0. |
Allowed Column Types: INT, FLOAT, DECIMAL
Validation
- Minimum parameters: 1
- Maximum parameters: 1
Examples
Zn([Sales])
Returns sales values as-is, but converts Null values to 0.
When to Use Zn vs Coalesce
Use Zn() specifically for numeric columns where you want null → 0. Use Coalesce() when you need more flexibility or are working with non-numeric data.