Logical Functions

Question 1

IN

Answer 1

Returns TRUE if any value in matches any value in .

The values in can be a Set, list of literal values, or a combined field.

Examples:

SUM([Cost]) IN (1000, 15, 200)

[SET] IN [COMBINED FIELD]

Question 2

AND

Answer 2

Performs a logical conjunction on two expressions.

Example:

IF (ATTR([Market]) = “Africa” AND SUM([Sales]) > [Emerging Threshold] )THEN “Well Performing”

Question 3

CASE

Answer 3

Performs logical tests and returns appropriate values. The CASE function evaluates expression, compares it to a sequence of values, value1, value2, etc., and returns a result. When a value that matches expression is encountered, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, then Null is returned.

CASE is often easier to use than IIF or IF THEN ELSE.

Typically, you use an IF function to perform a sequence of arbitrary tests, and you use a CASE function to search for a match to an expression. But a CASE function can always be rewritten as an IF function , although the CASE function will generally be more concise.

Many times you can use a group to get the same results as a complicated case function.

Examples:

CASE [Region] WHEN ‘West’ THEN 1 WHEN ‘East’ THEN 2 ELSE 3 END

CASE LEFT(DATENAME(‘weekday’,[Order Date]),3) WHEN ‘Sun’ THEN 0 WHEN ‘Mon’ THEN 1 WHEN ‘Tue’ THEN 2 WHEN ‘Wed’ THEN 3 WHEN ‘Thu’ THEN 4 WHEN ‘Fri’ THEN 5 WHEN ‘Sat’ THEN 6 END

Question 4

ELSE

Answer 4

Tests a series of expressions returning the value for the first true .

Example:

If [Profit] > 0 THEN ‘Profitable’ ELSE ‘Loss’ END

Question 5

ELSEIF

Answer 5

Tests a series of expressions returning the value for the first true .

Example:

IF [Profit] > 0 THEN ‘Profitable’ ELSEIF [Profit] = 0 THEN ‘Breakeven’ ELSE ‘Loss’ END

Question 6

END

Answer 6

Tests a series of expressions returning the value for the first true . Must be placed at the end of an expression.

Example:

IF [Profit] > 0 THEN ‘Profitable’ ELSEIF [Profit] = 0 THEN ‘Breakeven’ ELSE ‘Loss’ END

Question 7

IF

Answer 7

Tests a series of expressions returning the value for the first true .

Example:

IF [Profit] > 0 THEN ‘Profitable’ ELSEIF [Profit] = 0 THEN ‘Breakeven’ ELSE ‘Loss’ END

Question 8

IFNULL

Answer 8

Returns if it is not null, otherwise returns .

Example:

IFNULL([Profit], 0)

Question 9

IIF

Answer 9

Checks whether a condition is met, and returns one value if TRUE, another value if FALSE, and an optional third value or NULL if unknown.

Example:

IIF([Profit] > 0, ‘Profit’, ‘Loss’)

Question 10

ISDATE

Answer 10

Returns true if a given string is a valid date.

Example:

ISDATE(“2004-04-15”) = True

Question 11

ISNULL

Answer 11

Returns true if the expression is NULL (does not contain valid data).

Example:

ISNULL([Profit])

Question 12

MAX

Answer 12

Returns the maximum of a single expression across all records or the maximum of two expressions for each record.

Example:

MAX([Sales])

Question 13

MIN

Answer 13

Returns the minimum of an expression across all records or the minimum of two expressions for each record.

Example:

MIN([Profit])

Question 14

NOT

Answer 14

Performs logical negation on an expression.

Example:

IF NOT [Profit] > 0 THEN “Unprofitable” END

Question 15

OR

Answer 15

Performs a logical disjunction on two expressions.

Example:

IF [Profit] < 0 OR [Profit] = 0 THEN “Needs Improvement” END

Question 16

THEN

Answer 16

Tests a series of expressions returning the value for the first true .

Example:

IF [Profit] > 0 THEN ‘Profitable’ ELSEIF [Profit] = 0 THEN ‘Break even’ ELSE ‘unprofitable’ END

Question 17

WHEN

Answer 17

Finds the first that matches and returns the corresponding .

Example:

CASE [RomanNumberal] WHEN ‘I’ THEN 1 WHEN ‘II’ THEN 2 ELSE 3 END

Question 18

ZN

Answer 18

Returns if it is not null, otherwise returns zero.

Example:

ZN([Profit])