1. Using Incentive Compensation
  2. Logical Operations in the Expression Builder

Logical Operations in the Expression Builder

You can mimic your complex business rules using logical operations in the expression builder. This table provides descriptions, rules, and examples of available expression terms for logical operations.

Expression Term

Details

CHOICE

Syntax: (Condition 1*, Value 1*, Condition 2, Value 2, ...Condition n, Value n, Value Z*)

Inputs: The following list includes required and optional inputs:

  • Condition 1 (Required): Boolean (TRUE or FALSE)

  • Value 1 (Required): Can be a string or date or number

  • Condition 2...n (Optional): Boolean (TRUE or FALSE)

  • Value 2...n (Optional): Can be a string or date or number

  • Value z (Required): Can be a string or date or number

Return data type: String, Number or Date depending on value entered.

Description:

Allows users to use the equivalent of the IF, THEN, ELSE logic. Allows users to select different results based on different conditions.

It evaluates the conditions from first to last, so if Condition 1 is TRUE, then it will give Value 1. However, if Condition 1 isn't TRUE, then it will check if Condition 2 is TRUE. If it's true, then it will give Value 2, and so on. If no earlier condition is TRUE, then it will give the final value, Value z.

Examples: The following examples show the use of CHOICE.

CHOICE (IS_GREATER (Credit.Credit Amount, Measure.Target), Credit.Credit Amount, Measure.Target)

CHOICE (IS_GREATER (Credit.Credit Amount, 1000), 1, IS_EQUAL (Credit.Credit Amount, TO_NUMBER(Measure result.All Product Sales.ITD Output Achieved)), 2, 3)

Exceptions: If any of the input conditions aren't Boolean, then an error occurs.

IS_EQUAL

Syntax: IS_EQUAL (Value 1*, Value 2*)

Inputs: The following is a list of required inputs:

  • Value 1 (Required): Can be a string or date or number

  • Value 2 (Required): Can be a string or date or number

Return data type: Boolean (TRUE or FALSE)

Description: If Value 1 is equal to Value 2, then it returns TRUE. Otherwise, it returns FALSE. It's used within the input conditions of the CHOICE function.

Examples: The following examples show the use of IS_EQUAL.

IS_EQUAL (Credit Category Name (12313331333), 'Servers')

IS_EQUAL (Credit.Credit Amount, Measure.Target)

IS_EQUAL (TO_DATE('1-Dec-2017'), TO_DATE('2-Dec-2017'))

Exceptions: The following is a list of exceptions:

  • If any or both inputs are null, then it will return FALSE.

  • If any or both inputs cause an error, then it will cause an error.

IS_NULL

Syntax: IS_NULL (Value*)

Input: Value (Required): Can be a string or date or number or Boolean

Return data type: Boolean (TRUE or FALSE)

Description: If the value is null, then it returns TRUE. Otherwise, it returns FALSE. It's used within the input conditions of the CHOICE function.

Example: IS_NULL (Credit Category Name (12313331333))

Exception: If the input causes an error, then it will cause an error.

IS_GREATER

Syntax: IS_GREATER (Value 1*, Value 2*)

Inputs: The following is a list of required inputs:

  • Value 1 (Required): Can be a date or number

  • Value 2 (Required): Can be a date or number

Return data type: Boolean (TRUE or FALSE)

Description: If Value 1 is greater than Value 2 (or later in the case of dates), then it returns TRUE. Otherwise, it returns FALSE. It's used within the input conditions of the CHOICE function.

Examples: The following examples show the use of IS GREATER.

  • IS_GREATER (Credit.Credit Amount, Measure.Target)

  • IS_GREATER (TO_DATE('11-Dec-2017'), TO_DATE('2-Dec-2017'))

Exception: If any inputs are null, then it returns FALSE.

IS_LESS

Syntax: IS_LESS (Value 1*, Value 2*)

Inputs: The following is a list of required inputs:

  • Value 1 (Required): Can be a date or number

  • Value 2 (Required): Can be a date or number

Return data type: Boolean (TRUE or FALSE)

Description: If Value 1 is less than Value 2 (or earlier in the case of dates), then it returns TRUE. Otherwise, it returns FALSE. It's used within the input conditions of the CHOICE function.

Examples: The following examples show the use of IS_LESS:

  • IS_LESS (Credit.Credit Amount, Measure.Target)

  • IS_LESS (TO_DATE('11-Dec-2017'), TO_DATE('2-Dec-2017'))

Exceptions: If any inputs are null, then it returns FALSE.

IS_BETWEEN

Syntax: IS_BETWEEN (Value to evaluate*, Minimum Value*, Maximum Value*)

Inputs: The following is a list of required and optional inputs:

  • Value to evaluate (Required): Can be a date or number

  • Minimum Value (Required): Can be a date or number

  • Maximum Value (Required): Can be a date or number

Return data type: Boolean (TRUE or FALSE)

Description: If the value to evaluate falls between the minimum value and the maximum value, or is equal to either of them, then it returns TRUE. Otherwise, it returns FALSE. It's used within the input conditions of the CHOICE function.

Examples: The following examples show the use of IS_BETWEEN:

  • IS_BETWEEN (Credit.Credit Amount, TO_NUMBER(Plan component result.Management Bonus.PTD Output Achieved), Measure.Target)

  • IS_BETWEEN (TO_DATE('11-Dec-2017'), TO_DATE('2-Dec-2017'), TO_DATE('22-Dec-2017'))

Exceptions: If any inputs are null, then it returns FALSE.

AND

Syntax: AND (Condition 1*, Condition 2*, ...Condition n)

Inputs: The following is a list of required and optional inputs:

  • Condition 1 (Required): Boolean (TRUE or FALSE)

  • Condition 2 (Required): Boolean (TRUE or FALSE)

  • Condition 3 to n (Optional): Boolean (TRUE or FALSE)

Return data type: Boolean (TRUE or FALSE)

Description: If all conditions are TRUE, then it returns TRUE. Otherwise, it returns FALSE. It's used within the input conditions of the CHOICE function.

Example: AND (IS_GREATER (Credit.Credit Amount, Measure.Target), IS_EQUAL (Credit Category Name (12313331333), 'Servers'))

Exception: If any inputs are null or not Boolean or cause an error, then it will cause an error.

OR

Syntax: OR (Condition 1*, Condition 2*, ...Condition n)

Inputs: The following is a list of required and optional inputs:

  • Condition 1 (Required): Boolean (TRUE or FALSE)

  • Condition 2 (Required): Boolean (TRUE or FALSE)

  • Condition 3 to n (Optional): Boolean (TRUE or FALSE)

Return data type: Boolean (TRUE or FALSE)

Description: If any of the conditions are TRUE, then it returns TRUE. Otherwise, if all conditions are FALSE, then it returns FALSE. Its used within the input conditions of the CHOICE function.

Example: OR (IS_GREATER (Credit.Credit Amount, Measure.Target), IS_EQUAL (Credit Category Name (12313331333),'Servers'))

Exception: If any inputs are null or not Boolean or cause an error, then it will cause an error.

NOT

Syntax: NOT (Condition*)

Input: Condition (Required): Boolean (TRUE or FALSE)

Return data type: Boolean (TRUE or FALSE)

Description: It reverses the Boolean value of the input so that it returns TRUE if the input condition is false, and FALSE if the input condition is true. It's used within the input conditions of the CHOICE function.

Examples: The following examples show the use of NOT:

  • NOT (IS_LESS (2000, TO_NUMBER (Measure result.All Product Sales.ITD Output Achieved)))

  • NOT (IS_EQUAL (Credit Category Name (12313331333), 'Servers'))

Exception: If the input is null or not Boolean or causes an error, then it will cause an error.

IS_LIKE

Syntax: IS_LIKE (Value 1*, Value 2*)

Inputs: The following is a list of required inputs:

  • Value 1 (Required): String or number

  • Value 2 (Required): String or number. Here we use the percentage value (%) as a wildcard. Here % represents zero, one, or multiple characters.

Return data type: Boolean (TRUE or FALSE)

Description: If Value 1 matches a portion of Value 2, then it returns TRUE. Otherwise, it returns FALSE. The wildcard (%) determines which portion to match. It's used within the input conditions of the CHOICE function

Note: While creating the inputs and arguments add the percent sign (%) as part of the constant instead of entering it as an Operator button provided.

Examples: The following examples show the use of IS_LIKE.

  • IS_LIKE (UPPER(Participant.Currency Code), 'U%')

  • IS_LIKE ('apple', 'a%') will return TRUE

  • IS_LIKE ('apple', '%e') will return TRUE

  • IS_LIKE ('apple', '%pl%') will return TRUE

Exception: If any inputs are null, then it returns FALSE.

IS_IN

Syntax: IS_IN (Value*, In Value 1*,...In Value n)

Inputs: The following is a list of required and optional inputs:

  • Value (Required): Can be a string or date or number

  • In Value 1 (Required): Can be a string or date or number

  • In Value 3 to n (Optional): Can be a string or date or number

Return data type: Boolean (TRUE or FALSE)

Description: If Value is equal to any of the In Values, then it returns TRUE. Otherwise, it returns FALSE. It's used within the input conditions of the CHOICE function.

Examples: The following examples show the use of IS_IN:

  • IS_IN (Credit Category Name (12313331333), 'Servers', 'Laptops', 'Desktops')

  • IS_IN (Credit.Credit Amount, TO_NUMBER(Measure result.All Product Sales.ITD Output Achieved), Measure.Target, Measure.ITD Target)

Exception: If any or all inputs cause an error, then it will cause an error.