Formula Help

Report Center will be decommissioned on November 30, 2019
New customers who join after November 30, 2018, will only have access to Plutora Analytics. Existing customers with five or fewer Report Center reports will be able to use Report Center until May 31, 2019. Customers with over five custom reports will be able to use Report Center until November 30, 2019. For more information, please contact Plutora Support.

The Formula Help button in the Report Center redirects here. This page contains a guide to adding and using formulas. See Add a new column from a formula in Create A Report for how to use the information on this page.

Formula help v43200


Formulas are expressions made up of columns, constants, functions, and operators.

Columns are values that come from the data. Column titles are shown inside square brackets, for example, [ShippedDate]. Depending on their data type, they may be used as text strings, numbers, and date/time values.

Constants are specific fixed values that you enter into a formula.

Functions return values, usually computations based on columns and constants.

Operators perform arithmetic and logical comparisons.

Here are some examples:

[UnitPrice] * [Quantity]Multiply two data columns, UnitPrice and Quantity, to make an ExtendedPrice column.

[UnitPrice] * .04Multiply a data column by a constant value to calculate the tax applied to the price.

DateDiff("d", [OrderDate], [ShippedDate] )Get the number of days from the order to the shipment.

DateDiff("w", [ShippedDate], Now )Get the number of weeks since the shipment date.

WeekdayName( Weekday( [ShippedDate] ) )Return the name of the day of the week of the shipment date.

[LastName] + ", " + [FirstName]Concatenate columns and strings together. This might return: Smith, John

UCase( [LastName] + ", " + [FirstName] )Convert to upper case. This might return: SMITH, JOHN

 


Functions accept some number of values and return a single value.

VBScript evaluates formulas.  Click here for additional information and some more advanced VBScript functions.

Function Name Description Syntax Notes
Abs Returns the absolute value of a number. Abs(number) The absolute value of -5 is 5.
Date Returns the current date. Date()
DateAdd Adds or subtracts some interval of time from a date or time. DateAdd(interval, number, date) Interval may be:
yyyy=year
q=quarter
m=month
y=day of year
d=day
w=week
ww=week of year
h=hour
n=minute
s=secondIntervals must be quoted.
DateDiff Computes the difference between two dates. DateDiff(interval, date1, date2) Interval may be:
yyyy=year
q=quarter
m=month
y=day of year
d=day
w=week
ww=week of year
h=hour
n=minute
s=secondIntervals must be quoted.
DatePart Returns part of a date. DatePart(interval, date) Interval may be:
yyyy=year
q=quarter
m=month
y=day of year
d=day
w=week
ww=week of year
h=hour
n=minute
s=secondIntervals must be quoted.
DateSerial Combines date parts together to make a date. DateSerial(year, month, day)
DateValue Returns a date from a date string. The function can convert dates from many different formats. DateValue(text) DateValue("April 2, 1962")
Day Returns the day of the month. Possible return values are from 1-31. Day(date)
Exp Returns e raised to a power. e is the base of natural logarithms, called the antilogarithm. Exp(number)
FormatCurrency Format a number value into currency. FormatCurrency(number [,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])
FormatDateTime Formats a date. FormatDateTime(date[, NamedFormat]) Named Format may be vbGeneralDate, vbLongDate, vbShortDate, vbLongTime, or vbLongTime
FormatNumber Formats a number. FormatNumber(number [,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])
FormatPercent Formats a number as a percentage. FormatPercent(number [,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])
Hour Returns the hour of the day. Possible return values are 0-23. Hour(time)
IIF Returns one value or another, depending on if the expression is evaluates to True or False. IIF(expression,true-value,false-value) Expression is a formula that returns True or False.
For example, this would return Blue because 1 does not equal 2.
IIF(1=2,"Red","Blue")
InStr Returns the character location where one string is found within another string. InStr([start, ]string1, string2[, compare]) The first characters is at position 1. Instr() returns 0 when the string is not found.

Set compare to 1 for case-insensitive searches.

InStrRev Returns the character location where one string is found within another string. The search is started from the end of the string rather than the beginning. InStrRev(string1, string2[, start[, compare]])
Int Returns the integer portion of a number, removing any decimal places. Int(number)
IsDate Returns True if the text is a date. IsDate(text)
IsNumeric Returns True if the text is a number. IsNumeric(text)
LCase Converts all characters to lower case. LCase(text)
Left Returns the length number of characters from the left side of the input text. Left(text, length)
Len Returns the number of characters in the text. Len(text)
LTrim Removes the space characters from the left side of the text. LTrim(text)
Mid Returns characters from the middle of the text. Mid(text, start[, length]) start is the first character to be returned. The first character is at position 1. length is the number of characters to be returned.
Minute Returns the minute of the hour. Possible return values are 0-59. Minute(time)
Month Returns the month of the year. Possible return values are 1-12. Month(date)
MonthName Returns the name of the month. MonthName(month[, abbreviate]) Set abbreviate to True for an abbreviated month name.
Now Returns the current date and time. Now()
Replace Searches textSearch for textFind, replacing it with the textReplaceWith value. Replace(textSearch, textFind, textReplaceWith[, start[, count[, compare]]]) start is the starting character position to be searched.

count is the maximum number of replacements before stopping.

Set compare to 1 to replace characters regardless of case.

Replace("ABC", "abc", "123", 1, 1)

produces 123.

Right Returns the length number of characters from the right side of the input text. Right(text, length)
Rnd Returns a random number between 0 and 1. Rnd[(number)]
Round Returns a number rounded to a specified number of decimal places. Round(expression[, numdecimalplaces])
RTrim Removes any space characters from the right side of the text. RTrim(text)
Second Returns the second of the minute. Possible values are 0-59. Second(time)
Sgn Returns -1 if the number is negative. Returns 1 if the number is positive. Returns 0 if the number is 0. Sgn(number)
Space Returns text consisting of the number of spaces. Space(number)
Sqr Returns the square root of a number. Sqr(number)
String Returns text consisting of the character duplicated the number of times. String(number, character)
StrReverse Returns the text with the characters in reverse order. StrReverse(text)
TimeValue Returns a time value from a time string. The function can convert dates from many different formats. TimeValue(time)
Trim Removes space characters from both the left and rights sides of the text. Trim(text)
UCase Converts all characters to uppercase. UCase(text)
Weekday Returns the number of the day of the week. Possible return values are 1-7. Weekday(date, [firstdayofweek])
WeekdayName Returns the name of the day corresponding to the weekday number. WeekdayName(numberWeekday, abbreviate, firstdayofweek)
Year Returns the number of the year of the specified date. Year(date)

In the Syntax column, parameters in square brackets are optional.

 

Operators do arithmetic and logical comparisons. Formulas are evaluated through VBScript. Click here for more information about VBScript operators.

Operator Description
Negation
^ Exponentiation
* Multiplication
/ Division
Integer Division
Mod Modulus (Remainder)
+ Addition
Subtraction
+ String Concatenation
= Equals
<> Not Equals
< Less Than
> Greater Than
<= Less Than or Equal To
>= Greater Than or Equal To
Not Logical Not
And Logical And
Or Logical Or
( and ) Parenthesis to manage precedence.

 

You may represent true and false values as True and False.

If a Formula has an error, it will appear as either an empty value or a ?. Check the formula syntax to ensure it is correct.

 

Back to the top arrow

Was this article helpful?

1 found this helpful.