Search
Generic filters
Exact matches only

Functions & Formulas Reference Guide

Visual KPI supports an extensive list of functions and operators, including:

  • Math and Stat functions
  • Logical functions
  • Text functions
  • Lookup functions
  • Date and Time functions

Syntax and Supported Expressions

All equations in Visual KPI are of the format <[expression]>, where the expression is defined as below. Tag names or other data references are enclosed in square brackets, e.g. [tag1]. For example, a simple equation might look like this: <IF([tag1]>50,[tag2]/[tag3], 75)>. This equation will return the value of tag2 divided by the value of tag3 in all displays whenever the value of tag1 is greater than 50, otherwise it will return the constant value 75.

  • Variables of any type (native or user-defined).
  • Infinitely nested functions e.g.: cos(x + sin(y + z – tan(…))).
  • String literals enclosed by double quotes e.g. “my string”.
  • String literals use ‘\’ as the escape character.
  • Strings may contain ‘(‘, ‘)’ characters as well as text similar to variable and function names. These do not affect the processing of the expression.
  • Date literals enclosed by hash symbols: #yyyy-mm-dd#
  • Carriage returns and tabs within the expression string to help with user legibility.
  • The following case insensitive keywords ‘True’ and ‘False’ to represent Boolean values.
  • Variable and function names are case insensitive.

Math and Stat functions

ABS(double)
Return Typedouble
DescriptionAbsolute value
ACOS(double)
Return Typedouble
DescriptionArccosine
ACOT(double)
Return Typedouble
DescriptionArccotangent
ACSC(double)
Return Typedouble
DescriptionArccosecant
ASEC(double)
Return Typedouble
DescriptionArcsecant
ASIN(double)
Return Typedouble
DescriptionArcsine
ATAN(double)
Return Typedouble
DescriptionArctangent
AVERAGE(double, double, ...)
Return Typedouble
DescriptionReturns the average (arithmetic mean) of the arguments.
CEIL(double)
Return Typedouble
DescriptionThe smallest integer greater than or equal to the specified number.
COS(double)
Return Typedouble
DescriptionThe cosine of the given angle (in radians).
COSH(double)
Return Typedouble
DescriptionHyperbolic cosine
COT(double)
Return Typedouble
DescriptionCotangent
COTH(double)
Return Typedouble
DescriptionHyperbolic cotangent
COUNT(double, double, ...)
COUNT(double[ ])
Return Typedouble
DescriptionTotal count of parameters passed
CSC(double)
Return Typedouble
DescriptionCosecant
CSCH(double)
Return Typedouble
DescriptionHyperbolic cosecant
EXP(double)
Return Typedouble
DescriptionE (the base of natural logarithms) raised to the specified power
FLOOR(double)
INT(double)
Return Typedouble
DescriptionThe greatest integer less than or equal to the specified number
FORMAT(double, string style)
Return TypeString
DescriptionReturns a string expression formatted according to instructions contained in a format string expression.
For information on how to create the style argument, see the appropriate topic at MSDN:
Predefined Numeric Formats
User-Defined Numeric Formats
LN(double)
Return Typedouble
DescriptionNatural logarithm
LOG(double, double base)
Return Typedouble
DescriptionLogarithm to the specified base
LOG10(double)
Return Typedouble
DescriptionLogarithm to base 10
MAX(double, double, ...)
MAX(double[ ])
Return Typedouble
DescriptionMaximum
MIN(double, double, ...)
MIN(double[ ])
Return Typedouble
DescriptionMinimum
NEG(double)
Return Typedouble
DescriptionNegation
POWER(double, double power)
Return Typedouble
DescriptionThe specified number raised to the specified power
RAND([double max])
Return Typedouble
DescriptionRandom number between 0 and max. If max is undefined then RAND() returns random number between 0 and 1.
ROUND(double d, [int decimals])
Return Typedouble
DescriptionReturns the number with the specified precision nearest the specified value.
Parametersd: A number to be rounded.
decimals: The number of significant fractional digits (precision) in the return value. Ranges from 0 to 28.
Return ValueThe number nearest d with precision equal to decimals. If d is halfway between two numbers, one of which is even and the other odd, then the even number is returned. If the precision of d is less than decimals, then d is returned unchanged.
RemarksThe decimals parameter specifies the number of significant fractional digits in the return value and ranges from 0 to 28. If decimals is zero, then a whole number is returned. This kind of rounding is sometimes called rounding to nearest, or banker's rounding.
SEC(double)
Return Typedouble
DescriptionSecant
SECH(double)
Return Typedouble
DescriptionHyperbolic secant
SIN(double)
Return Typedouble
DescriptionThe sine of the given angle (in radians).
SINH(double)
Return Typedouble
DescriptionHyperbolic sine
SQRT(double)
Return Typedouble
DescriptionSquare root
SUM(double, double, ...)
SUM(double[ ])
Return Typedouble
DescriptionSum of the specified numbers
TAN(double)
Return Typedouble
DescriptionTangent
TANH(double)
Return Typedouble
DescriptionHyperbolic tangent

Logical Functions

IIF(bool condition, expression1, expression2
IF(bool condition, expression1, expression2)
Return Typeany
DescriptionIf condition evaluates to TRUE, expression1 is returned. If conditionevaluates to FALSE, expression2 is returned.
This function accepts any type for its second and third parameters as long as they are both of the same type.
AND(boolean, boolean, …)
Return Typeboolean
DescriptionReturns true if all its arguments are true; returns false if any argument is false
OR(boolean, boolean, …)
Return Typeboolean
DescriptionReturns true if any argument is true; returns false if all arguments are false
IsNull(object)
IsDBNull(object)
Return Typeboolean
DescriptionDetermines whether or not a given expression is a DBNull value
IsNumeric(object)
Return Typeboolean
DescriptionDetermines whether or not a given expression is a Numeric Numeric value
IsBetween(object,low,high)
Return Typeboolean
DescriptionDetermines whether or not a given NUMERIC expression is between a low and high value
IsEqualTo(object, value)
Return Typeboolean
DescriptionDetermines whether or not a given NUMERIC expression is equal to a value
IsGreaterThan(object, value)
Return Typeboolean
DescriptionDetermines whether or not a given NUMERIC expression is greater than a value
IsLessThan(object, value)
Return Typeboolean
DescriptionDetermines whether or not a given NUMERIC expression is less than a value

Text Functions

Concat(object1, object2, … objectN)
Return Typestring
DescriptionReturns a single concatenated string from a list of strings. doubles, ints, booleans.
ConcatSep(object1, object2, … objectN, string delimiter)
Return Typestring
DescriptionReturns a single concatenated and delimited string from a list of strings. doubles, ints, booleans
Find(string textToFind, string textToSearch, int startIndex)
Return Typeint
DescriptionReturns the zero-based index of the first occurrence of a textToFind, within a textToSearch string. The search starts at a specified character position (zero-based).
Left(string, int nCount)
Return Typestring
DescriptionReturns the first (leftmost) nCount characters from a string.
Len(string)
Return Typeint
DescriptionReturns the number of characters in a string
Lower(string)
Return Typestring
DescriptionConverts all uppercase letters in a text string to lowercase.
Mid(string, int nFirst, int nCount)
Return Typestring
DescriptionReturns a substring of length nCount characters from a string, starting at position nFirst (zero-based).
Substitute(string text, string oldText, string newText)
Return Typestring
DescriptionSubstitutes newText for oldText in a text string.
Trim(string)
Return Typestring
DescriptionRemoves all occurrences of white space characters from the beginning and end of a string.
Upper(string)
Return Typestring
DescriptionConverts all lowercase letters in a text string to uppercase.

Lookup Functions

IN(string ItemToFind, string ListItem1, string ListItem2, …
IN(string ItemToFind, string[] ListItem)
Return Typeboolean
DescriptionLooks for ItemToFind in the specified list of items. Returns true if item is found in the list, false otherwise.

Date and Time Functions

NOW()
Return TypeDateTime
DescriptionCurrent date and time according to the setting of your computer’s system date and time.
TODAY()
Return TypeDateTime
DescriptionCurrent date. Time part of the day is zero (midnight).
YEAR(date)
Return Typeint
DescriptionNumber representing the year.
MONTH(date)
Return Typeint
DescriptionNumber between 1 and 12, inclusive, representing the month of the year.
DAY(date)
Return Typeint
DescriptionNumber between 1 and 31, inclusive, representing the day of the month.
WEEKDAY(date)
Return TypeDayOfWeek enumeration
DescriptionNumber representing the day of the week.
HOUR(date)
Return Typeint
DescriptionNumber between 0 and 23, inclusive, representing the hour of the day.
MINUTE(date)
Return Typeint
DescriptionNumber between 0 and 59, inclusive, representing the minute of the hour.
SECOND(date)
Return Typeint
DescriptionNumber between 0 and 59, inclusive, representing the second of the minute.
DATEADD(interval, double number, DateTime date)
Return TypeDateTime
DescriptionNumber between 0 and 59, inclusive, representing the minute of the hour.
Parametersinterval: DateInterval enumeration value or string expression representing the time interval you want to add. The intervalargument can have one of the settings listed here.
number: The number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past). Any fractional part of number is ignored.
date: date to which the interval is added.
ExampleThe following expressions are equivalent:
DateAdd(dtMonth, 1, #2005-12-31#) DateAdd(“m”, 1, #2005-12-31#) DateAdd(2, 1, #2005-12-31#)In this example, DateAdd returns #2006-01-31#.
DATEDIFF(interval, DateTime date1, DateTime date2 [, firstdayofweek [,firstweekofyear]])
Return TypeSystem.Int64
DescriptionReturns the number of intervals between two dates.
Parametersinterval: DateInterval enumeration value or string expression representing the time interval you want to use as the unit of difference between date1 and date2. The interval argument can have one of the settings listed here.
date1, date2: Date expressions. Two dates you want to use in the calculation.
firstdayofweek: Optional. A value chosen from the FirstDayOfWeek enumeration that specifies the day of the week. If not specified, Sunday is assumed.
firstweekofyear: Optional. A value chosen from the FirstWeekOfYear enumeration that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
RemarksIf Date1 represents a later date and time than Date2, DATEDIFF returns a negative number.
If Interval is set to DateInterval.Year, DateInterval.Quarter, or DateInterval.Month the return value is the number of date and time boundaries crossed between Date1 and Date2.
Note When comparing December 31 to January 1 of the following year, DATEDIFF returns 1 for DateInterval.Year, DateInterval.Quarter, or DateInterval.Month, even though only one day has elapsed.
ExampleThe following expressions are equivalent:
DateDiff(dtYear, #2005-12-31#, #1995-12-31#) DateDiff(“yyyy”, #2005-12-31#, #1995-12-31#) DateDiff(0, #2005-12-31#, #1995-12-31#)In this example, DateDiff returns -10.
DATEPART(interval, DateTime date [, firstdayofweek [,firstweekofyear]])
Return TypeSystem.Int32
DescriptionReturns an Integer value containing the specified component of a given Date value.
Parametersinterval: DateInterval enumeration value or string expression representing the part of the date/time value you want to return. The interval argument can have one of the settings listed here.
date: Date expression you want to evaluate.
firstdayofweek: Optional. A value chosen from the FirstDayOfWeek enumeration that specifies the day of the week. If not specified, Sunday is assumed.
firstweekofyear: Optional. A value chosen from the FirstWeekOfYear enumeration that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
ExampleThe following expressions are equivalent:
DatePart(dtQuarter, #2005-12-31#) DatePart(“q”, #2005-12-31#) DatePart(1, #2005-12-31#)In this example, DatePart returns 4.
DATE(int year, int month, int day [, int hour, int minute, int second])
Return TypeDateTime
DescriptionA Date value representing a specified year, month, day, hour, minute, and second.
Parametersyear: The year (1 through 9999).
month: The month (1 through 12).
day: The day (1 through the number of days in month).
hour: Optional. The hours (0 through 23).
minute: Optional. The minutes (0 through 59).
second: Optional. The seconds (0 through 59).
Return ValueA Date value representing a specified year, month, day, hour, minute, and second.
MIN(DateTime date1, DateTime date2, …)
MIN(DateTime[ ] date)
Return TypeDateTime
DescriptionReturns the smallest date in a set of date values.
MAX(DateTime date1, DateTime date2, …)
MAX(DateTime[ ] date)
Return TypeDateTime
DescriptionReturns the largest date in a set of date values.
FORMAT(DateTime, string style)
Return Typestring
DescriptionReturns a string expression formatted according to instructions contained in a format string expression.
For information on how to create the style argument, see the appropriate topic at MSDN:
Predefined Date Formats
User-Defined Date Formats

Operators (Input Types)

Arithmetic Operators

OperatorNumDateStrBoolDescription
plus(+)yesyesPositive sign or arithmetic addition
yesNegative sign or arithmetic subtraction
*yesMultiplication
/yesDivision
%yesRemainder (modulus) obtained by dividing one numeric expression into another
^yesPower

Comparison Operators

OperatorNumDateStrBoolDescription
<yesyesyesLess than
>yesyesyesGreater than
<=yesyesyesLess than or equal
>=yesyesyesGreater than or equal
equals (== or =)yesyesyesyesEquality
!= or <>yesyesyesyesInequality

Logical Operators

OperatorNumDateStrBoolDescription
AND
&&
yesLogical AND
OR
||
yesLogical OR
NOT
!
yesLogical Negation

Bitwise Operators

OperatorNumDateStrBoolDescription
&yesBitwise AND operator.
|yesBitwise OR operator.
!&yesBitwise exclusive-OR operator.
~yesBitwise complement operator.

Constants

ConstantValue
PI3.141592654
E2.718281828
TRUE1
FALSE0

Date Interval

ConstantNumeric ValueString Expression
dtYear0yyyy
dtQuarter1q
dtMonth2m
dtDayOfYear3y
dtDay4d
dtWeekOfYear5ww
dtWeekday6w
dtHour7h
dtMinute8n
dtSecond9s

FirstDayOfWeek

ConstantNumeric Value
dtSystem0
dtSunday1
dtMonday2
dtTuesday3
dtWednesday4
dtThursday5
dtFriday6
dtSaturday7

FirstWeekOfYear

ConstantNumeric Value
dtSystem0
dtJan11
dtFirstFourDays2
dtFirstFullWeek3

 

 

 

Was this article helpful?

Related Articles