Formulas Explained

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. 

Note: The SQL and Formulas used in the Report Center are proprietary/hybrid versions of SQL. "Classic" SQL formulas commonly used in Excel do not function in the Report Center. If you are unfamiliar with SQL, start with this article before continuing. 

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.

mceclip0__74_.png

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
Was this article helpful?
0 out of 0 found this helpful

Still looking for your answer? How Can We Help?