Data Validation Conditional Operators

Conditional operators in the data validation rule builder can include these types of comparisons:

Numeric comparisons, using these operators: =, !=, <, <=, >, >=
String value comparisons, using these operators: Equals, Not Equals, Contains, Starts With, Ends With, In, and Not In

Rules can compare cells with different data types, for example, text and Smart List. The data type of the cell is honored if the referenced value always comes from one cell. This is the case when using Current Cell Value and Cross Dim Member to refer to a cell value. In cases where the value being compared comes from multiple cells (such as row value, column value, and cell value), the data type is defaulted to double.

Note these considerations when comparing values for these data types:

For double, a string representation is used for the double value, such as “123.45.” If the double is a whole number with no fractional part, such as 123.00, the integer value is used, for example, “123.”
For Smart Lists, rules use the Smart List name and the numeric value as stored in Essbase. They do not use the Smart List label because it can change for different users, depending on the user locale.
For text, rules use only the text value for comparison.
All other data types (currency, non-currency, percentage, and date) are treated as double.
For date, rules use the numeric value as stored in Essbase for comparison. For example. if a user types 12/11/1999, assuming the format is MM/DD/YYYY, the value is stored in Essbase as 19991211, and this numeric value is used for comparison.

Table 58. Examples of Results for Starts With, Ends With, and Contains

Operator	Compare Value	Compare To Value
Starts With	2.0	2
	1234.0	12.0
	101.0	10
	2.0	2.0
	2.5	“2.”
	“YearTotal”	“Year”
Ends With	2.0	2.0
	2.0	2
	2.5	5
	2.5	“.5”
	“YearTotal”	“al”
	“YearTotal”	“Total”
Contains	2.0	2.0
	2.0	2
	2.5	5
	2.5	“.5”
	2.5	2.5
	23.567	3.5
	23.567	67
	23.567	“23.”
	23.567	“.56”
	“YearTotal”	“al”