Logical Functions

ABOUT THIS DOCUMENT: 

This document provides support for logical functions users can access in the Sandbox.

Explains in detail the logical functions used in Studio. This includes Between, Choose, Coalesce, If, In, Isnotnull, Isnull, Switch, and Zn functions that enable users to perform different types of analysis. The following is a more detailed list of these functions and how they work.

Between

Returns whether a value lies in the range between two other values.

Formula

Between(value, lower bound, upper bound)

• value (required) The input value to check.
• lower bound (required) The lower bound of the range to check.
• upper bound (required) The upper bound of the range to check.

Example

Between([Invoice Date], MakeDate(2015, 1, 1), MakeDate(2020, 1, 1))

• Returns TRUE if the date in the column name [Date] 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

Choose

Returns the value with the corresponding index position. When an index number is provided and a corresponding value does not exist, a Null result is returned.

Formula

Choose(index number, value 1, ...)

• index number (required) The index number of the value returned. Index value needs to be a number, a formula or column that references a number.
• value 1 (required) The values from which to choose. At least one value must be provided.
• value 2+ (optional) Additional values are optional.

Example

Choose(1, "value 1", "value 2", "value 3")

• Returns value 1.

Choose(Weekday([Date]), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

• Returns the name of the day of the week falling on the date in the [Date] column.

Coalesce

Returns the first non-Null value from the arguments provided.

Formula

Coalesce(argument 1, ...)

• argument 1 (required) Arguments can be numbers, equations, column references or functions. If all values are Null, Null is returned.
• argument 2+ (optional) Coalesce can evaluate several arguments.

NOTE: Arguments must all be of the the same  type. All inputed arguments must be numbers, dates, or string, without mixing types. If you need to evaluate different types of arguments, you can use text() to force the Coalesce function to read the input as a string.

Example

Coalesce(Null, 1/0, 1/1, 1/2) = 1

• The third value is the first non-Null value

Coalesce(1/0, 0) = 0

• The second value is the first non-Null value

Coalesce([Sales], 0)

• Converts all the null values in [Sales] to 0.

Coalesce([profit]/[sales], 0)

• Returns profit per sale for when sales >0; returns 0 if there are no sales. This construction avoids Nulls when the formula could face situations that divide by 0.

If

Returns the value following the first condition that is met.

Formula

If(condition 1, value 1, [condition 2], [value 2],..., [else])

• If Condition 1 is True, Value 1 is returned. If Condition 2 is True, Value 2 is returned. If all conditions are false, the Else value listed last in the function is applied.
• condition 1 (required) Logical condition that returns a result that is either True or False. If the condition is True, the function returns the following value. Note: you can use operators in conditions.
• value 1 (required) The value to be returned if its preceding condition is True.
• condition 2+, value 2+ (optional) Several , pairs can be listed in a single function. Every supplied condition must have a corresponding value.
• else (optional) The value to be returned if no conditionals evaluate as True. If no condition is supplied, a Null result is returned when no conditions are met.

Example

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.

If([Product Family] = "Cameras & Camcorders" OR [Product Family] = "Camera Accessories", "Photography")

• Categorize records in the product family with an overarching product type using the "OR" operator.

In

Test if a value matches any value in a subsequent list of values. Returns True if the first value matches any of the subsequent values. Returns False if no values are matched.

Formula

In(value, candidate 1,[candidates 2+])

• value (required) The value to test.
• candidate 1 (required) The candidates to test value against. You must supply at least one candidate.
• candidates 2+ (optional) You can supply additional candidates to test against.

Example

In("green", "red", "green", "blue")

• Returns True.

In("yellow", "red", "green", "blue")

• Returns False.

In([Customer ID], 2000, 3000, 4000)

• Returns True for rows where Customer ID is 2000, 3000, or 4000. Returns False for all other rows.

In([Customers], "Customer 1", "Customer 2")

• Returns True for rows where [Customers] matches “Customer 1” or “Customer 2”. Returns false for all other rows.

In("John Smith", [Customers], [Buyers])

• Returns True for rows where “John Smith” appears in either the Customers or the Buyers columns.

IsNotNull

Returns True if the argument contains valid data, and False if the argument is Null (does not contain valid data).

Formula

IsNotNull(argument)

• argument (required) Any argument can be provided. The function can analyze one argument at a time. IsNotNull is often applied to a column of data to test for non-Null values.

Example

IsNotNull([Sales])

• Function will return True for rows where sales data exists and False for rows where there is no data.

IsNull

Returns True if the argument is Null (does not contain valid data), and False otherwise.

Formula

IsNull(argument)

• argument (required) Any argument can be provided. The function can analyze one argument at a time. IsNull is often applied to column of data to test for Null values.

Example

IsNull([Sales])

• Function will return False for rows where sales data exists and True for rows where there is no data.

Switch

Returns the result corresponding to the first matching value.

Formula

Switch(value, case 1, result 1, [case 2], [result 2], ... , [else])

• value (required) The value to test.
• case 1 (required): The case to test the value against. If the matches , the following is returned.
• result 1 (required): The result to be returned if its preceding case matches the input value.
• case 2+, result 2+ (optional): Several pairs can be listed in a single function. Every supplied case must have a corresponding result.
• else (optional): The result to be returned if no cases match the value. If no condition is supplied, a Null result is returned when no cases are met.

Example

Switch(1, 0, "None", 1, "One", "Many")

• Returns "One"

Switch(2, 0, "None", 1, "One", "Many")

• Returns "Many"

Zn

Returns the argument if it is not null, otherwise returns zero.

This function is helpful if you want to use zero values instead of null values.

Formula

Zn(argument)

• argument (required) The number value to be checked.

Examples

(1) Returns 3.

Zn(3)

(2) Returns 0.

Zn(null)

(3) Returns 0 for every row in the column that is null.

Zn([Quantity])