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 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 that the SQL and Formulas used in the Report Center is a proprietary/hybrid version 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 Format 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. 
Case  Returns one value or another, depending on if the expression evaluates to True or False. 
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. 
Date  Returns the current date, with time = 00:00:00. 
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
 - Negation
 * 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?