Formula Help
This article explains how to use Formulas in Agility.
Overview
Formulas are expressions that must contain data columns, and can include constants, functions, and operators.
- Columns values that come from the data. Their names are enclosed in square brackets, like [ShippedDate]. Depending on their data type, they may be used as text strings, numbers, and date or time values.
- 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. Logical comparisons must be used with Case statements.
Here are some examples:
[UnitPrice] * [Quantity]
Multiply two data columns, UnitPrice and Quantity, to make an ExtendedPrice column.
[UnitPrice] * .04
Multiply a data column by an 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 weekdays since the shipment date.
DateName( dw, 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
Upper( [LastName] + ', ' + [FirstName] )
Convert to upper case. This might return: SMITH, JOHN
Functions
Functions accept some number of values and return a single value.
| Function Name | Description | Syntax | Notes |
|---|---|---|---|
| Abs | Returns the absolute value of a number. | Abs(number) | The absolute value of -5 is 5. |
| Case | Returns one value or another, depending on if the expression is evaluates to True or False. | Case variable When value Then return value Else other value End | variable has a values that will be compared. value is something that may be matched in the variable. return value is returned when the variable and value match. other value is returned when there are no matches. For example, Case [Color] When 'pink' Then 'red' When 'amber' Then 'yellow' Else 'gray' End |
| Date | Returns the current date. | Getdate() | |
| 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=weekday ww=week of year hh=hour n=minute s=second |
| 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=weekday ww=week of year hh=hour n=minute s=second |
| 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=weekday ww=week of year hh=hour n=minute s=second |
| DateSerial | Combines date parts together to make a date. | Cast(Cast(year AS varchar) + '-' + Cast(month AS varchar) + '-' + Cast(day AS varchar) AS DATETIME) | |
| DateValue | Returns a date from a date string. The function can convert dates from many different formats. | Convert(DATETIME, date_string) | date_string is a string value which represents a date or date/time. |
| Day | Returns the day of the month. Possible return values are from 1-31. | Day(date) | |
| FormatCurrency | Format a number value into currency. | '$' + Convert(varchar(12), NumericValue, 1) | |
| FormatDateTime | Formats a date. | Convert(varchar, date, 120) | "120" is the code that dictates the date format. More codes are available for different formats. |
| FormatNumber | Formats a number. | Convert(varchar(12), NumericValue, 1) | |
| FormatPercent | Formats a number as a percentage. | Convert(varchar,Convert(Decimal(6,2), number)) + '%' | |
| Hour | Returns the hour of the day. Possible return values are 0-23. | DatePart(hh, date) | |
| Case | Returns one value or another, depending on if the expression is evaluates to True or False. | Case When Expression Then True Else False End | Expression is a formula that returns True or False. For example, this would return "Blue", because 1 does not equal 2. Case When 1=2 Then "Red" Else "Blue" End |
| InString | Returns the character location where one string is found within another string. | CharIndex(expressionToFind ,expressionToSearch [,start_location]) | Returns the index value if the string is found, else returns 0. |
| Int | Returns the integer portion of a number, removing any decimal places. | Round(Floatvalue,0,1) | ex. ROUND(150.75, 0) = 151.00 ROUND(150.75, 0, 1) = 150.00 |
| IsDate | Returns True if the text is a date. | IsDate(text) | Return 1 if True, else returns False |
| IsNumeric | Returns True if the text is a number. | IsNumeric(text) | Return 1 if True, else returns False |
| Lower | Converts all characters to lower case. | Lower(text) | |
| Left | Returns the "length" number of characters from the left side of the input text. | Left(character_expression, integer_expression) | |
| 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. | Substring(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. | DatePart(mi,date) | |
| Month | Returns the month of the year. Possible return values are 1-12. | Month(date) | |
| MonthName | Returns the name of the month. | DateName(month,date) | |
| Now | Returns the current date and time. | Getdate() | |
| Replace | Searches textSearch for textFind, replacing it with the textReplaceWith value. | Replace(string1,string_to_replace,[replacement_string]) | |
| Right | Returns the "length" number of characters from the right side of the input text. | Right(str,len) | |
| Rand | Returns a random number between 0 and 1. | Rand([seed]) | Seed is an integer expression (tinyint, smallint, or int) that gives the seed value. This parameter is Optional. |
| Round | Returns a number rounded to a specified number of decimal places. | Round(number,length ) | ex. ROUND(150.75, 0) = 151.00 ROUND(150.75, 0, 1) = 150.00 |
| 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. | DatePart(ss,date) | |
| Sign | Returns -1 if the number is negative. Returns 1 if the number is positive. Returns 0 if the number is 0. | Sign(number) | |
| Space | Returns text consisting of the number of spaces. | Space (number) | |
| Square | Returns the square of a number. | Square(number) | |
| String | Returns text consisting of the character duplicated the number of times. | Replicate(text,count) | |
| Reverse | Returns the text with the characters in reverse order. | Reverse(text) | |
| TimeValue | Returns a time value from a time string. The function can convert dates from many different formats. | Convert(varchar(8),Convert(datetime,text),108) | |
| Trim | Removes space characters from both the left and rights sides of the text. | LTrim(RTrim(text)) | |
| Upper | Converts all characters to upper case. | Upper(text) | |
| Weekday | Returns the number of the day of the week. Possible return values are 1-7. | DatePart(wk,date) | |
| WeekdayName | Returns the name of the day corresponding to the weekday number. | DateName(dw,date) | |
| Year | Returns the number of the year of the specified date. | Year(date) |
In the Syntax column, parameters in square brackets are optional.
Operators
Operators do arithmetic and logical comparisons
| Operator | Description |
|---|---|
| - | Negation |
| ^ | Exponentiation: Power( m, n ). Function returns m raised to the nth power. |
| * | Multiplication |
| / | Division |
| % | Modulus: m%n. |
| + | Addition |
| - | Subtraction |
| + | String Concatenation |