Formulas are expressions made up of data columns, constants, functions, and operators that access or manipulate data from within the database.
Columns are values that come from the data. Their names are enclosed in square brackets, like [ShippedDate].
Constants are simply specific fixed values that you enter into a Formula.
Functions return values, usually computations based on columns and constants.
Operators do arithmetic and logical comparisons.
Functions
The following table explains every function available in an analysis. You can find more information regarding syntax and technical notes by clicking Formula Help from the Formula tab of an Analysis.
Function Name | Description |
---|---|
Abs | Returns the absolute value of a number. |
Case | Returns one value or another, depending on if the expression evaluates to True or False. |
DateAdd | Adds or subtracts some interval of time from a date or time. |
DateDiff | Computes the difference between two dates. |
DatePart | Returns part of a date. |
DateSerial | Combines date parts together to make a date. |
DateValue | Returns a date from a date string. The function can convert dates from many different formats. |
Day | Returns the day of the month. Possible values are from 1-31. |
FormatCurrency | Formats a number value into currency. |
FormatDate | Formats a date. |
FormatDateTime | Formats a date with time. |
FormatNumber | Formats a number. |
FormatPercent | Formats a number as a percentage. |
Hour | Returns the hour of the day. Possible return values are 0-23. |
InString | Returns the character location where one string is found within another string. |
Int | Returns the integer portion of a number, removing any decimal places. |
IsDate | Returns True if the text is a date. |
IsNumeric | Returns True if the text is a number. |
Lower | Converts all characters to lower case. |
Left | Returns the "length" number of characters from the left side of the input text. |
Len | Returns the number of characters in the text. |
LTrim | Removes the space characters from the left side of the text. |
Mid | Returns characters from the middle of the text. |
Minute | Returns the minute of the hour. Possible return values are 0-59. |
Month | Returns the month of the year. Possible return values are 1-12. |
MonthName | Returns the name of the month. |
Now | Returns the current date and time. |
Power | Exponentiation: Power( m, n ). Function returns m raised to the nth power. |
Replace | Searches textSearch for textFind, replacing it with the textReplaceWith value. |
Right | Returns the "length" number of characters from the right side of the input text. |
Rand | Returns a random number between 0 and 1. |
Round | Returns a number rounded to a specified number of decimal places. |
RTrim | Removes any space characters from the right side of the text. |
Second | Returns the second of the minute. Possible values are 0-59. |
Sign | Returns -1 if the number is negative. Returns 1 if the number is positive. Returns 0 if the number is 0. |
Space | Returns text consisting of the number of spaces. |
Square | Returns the square of a number. |
String | Returns text consisting of the character duplicated the number of times. |
Reverse | Returns the text with the characters in reverse order. |
TimeValue | Returns a time value from a time string. The function can convert dates from many different formats. |
Trim | Removes space characters from both the left and rights sides of the text. |
Upper | Converts all characters to upper case. |
Weekday | Returns the number of the day of the week. Possible return values are 1-7. |
WeekdayName | Returns the name of the day corresponding to the weekday number. |
Year | Returns the number of the year of the specified date. |
Operators
Operators do arithmetic and logical comparisons.
Operator | Description |
---|---|
* | Multiplication |
/ | Division |
% | Modulus: m%n |
+ | Addition and string concatenation |
- | Subtraction |