Column Formulas
Reports use formula columns to read and manipulate data of other columns. Use adding, subtracting, multiplication, division, and logical operands as well as logic functions and math functions. Refer to the following formula quick references.
Operators
Name | Symbols |
Multiplication, Division, Modulus | *,/,% |
Addition, Subtraction | +, - |
Logical AND, XOR, OR | &, ^, | |
Logic Function
Name | Description | Example |
(<condition> ? <true> : <false>) | If condition evaluates to "true", returns <true>, otherwise returns <false>. | (a > b ? a + b : 0) |
Math Functions
Name | Description | Example |
Abs | Returns absolute value of a specified number. | Abs(x) |
Acos | Returns the angle whose cosine is the specified number. | Acos(x) |
Asin | Returns the angle whose sine is the specified number. | Asin(x) |
Atan | Returns the angle whose tangent is the specified number. | Atan(x) |
Atan2 | Returns the angle whose tangent is the quotient of two specified numbers. | Atan2(x,y) |
Ceiling | Returns the smallest integer greater than or equal to the specified number. | Ceiling(x) |
Cos | Returns the cosine of the specified angle. | Cos(x) |
Cosh | Returns the hyperbolic cosine of the specified angle. | Cosh(x) |
Exp | Returns e raised to the specified power. | Exp(x) |
Floor | Returns the largest integer less than or equal to the specified number. | Floor(x) |
Remainder | Returns the remainder resulting from the division of a specified number by another specified number. | Remainder(x,y) |
Log | Returns the logarithm of a specified number. | Log(x) |
Log10 | Returns the base 10 logarithm of a specified number. | Log10(x) |
Max | Returns the larger of two specified numbers. | Max(x,y) |
Min | Returns the smaller of two numbers | Min(x,y) |
Pow | Returns a specified number raised to the specified power. | Pow(x,y) |
Round | Rounds a value to the nearest integer or specified number of decimal places. | Round(x), Round(x, d) |
Sign | Returns a value (-1 or 1) indicating the sign of a number. | Sign(x) |
Sin | Returns the sine of the specified angle. | Sin(x) |
Sinh | Returns the hyperbolic sine of the specified angle. | Sinh(x) |
Sqrt | Returns the square root of a specified number. | Sqrt(x) |
Tan | Returns the tangent of the specified angle. | Tan(x) |
Tanh | Returns the hyperbolic tangent of the specified angle. | Tanh(x) |
Truncate | Calculates the integral part of a number. | Truncate(x) |
MaxN | Returns the largest of a set of numbers. | MaxN(x,y,z,a,b,...) |
MinN | Returns the smallest of a set of numbers. | MinN(x,y,z,a,b,...) |
Avg | Returns the average of a set of numbers. | Avg(x,y,z,a,b,...) |
Sum | Returns the sum of a set of numbers. | Sum(x,y,z,a,b,...) |
Pl | Returns the value of pi. | PI() |
E | Returns the value of e. | E() |
String Functions
Name | Description | Example | Result |
Min | Returns the minimum of two string values. | Min('a', 'b') | a |
Max | Returns the maximum of two string values. | Max('y', 'z') | z |
MinN | Returns the minimum of n string values. | MinN('a', 'b', 'c') | a |
MaxN | Returns the maximum of n string values. | MaxN('x', 'y', 'z') | z |
Format | Returns a string with n values inserted and formatted. | Format('I have {0:0.00} hours in {1} shifts', 12, 2) | I have 12.00 hours in 2 shifts |
Left | Returns the leftmost number of specified characters. | Left('aaabbbcccdddeeefff',5) | aaabb |
Right | Returns the rightmost number of specified characters. | Right('A good thing', 5) | thing |
Substring | Extracts a string given a larger one. Uses start index (starts a 0) and length. | Substring('A good thing', 2, 4) | good |
Replace | Replaces all occurrences of a string within a string. | Replace('A good thing is good', 'good', 'bad') | A bad thing is bad |
Reverse | Reverses a string | Reverse('AbCd') | dCbA |
PadLeft | Creates a fixed length string and pads the left with a given character (or space if none specified). | PadLeft('333', 10, 'x') | xxxxxxx333 |
PadRight | Creates a fixed length string and pads the right with a given character (or space if none specified). | PadRight('444', 5, 'b') | 444bb |
Trim | Removes leading and trailing whitespace | Trim(' dog ') | dog |
ToLower | Converts a string to lowercase characters | ToLower('MY Car') | my car |
ToUpper | Converts a string to uppercase characters | ToUpper('some title') | SOME TITLE |
Length | Return the number of characters | Length('aaabbb') | 6 |
ToDate | Converts a string to a date | ToDate('6/1/2014 10 PM') | 6/1/2014 22:00 |
Contains | Checks if the second string is contained in the first. | Contains('aaabbbccc', 'bc') | 1 |
|
| Contains('aaabbbccc', 'R') | 0 |
IsEmpty | Returns true if value is null or zero length, false otherwise. | IsEmpty('') | TRUE |
Concat | Appends N strings together. | Concat(55, 33, 'test message') | 5533test message |
Date Functions
Name | Description | Example | Result |
Min | Returns the minimum of two date values. | Min('6/1/04', '6/1/10') | 6/1/2004 |
Max | Returns the maximum of two date values. | Max('6/1/04', '6/1/10') | 6/1/2010 |
MinN | Returns the minimum of n date values. | MinN('6/1/04', '6/1/10','1/1/1970') | 1/1/1970 |
MaxN | Returns the maximum of n date values. | Max('6/1/04', '6/1/10','1/1/1970') | 6/1/2010 |
TotalWeeks | Returns the total (fractional) number of weeks between two dates. | TotalWeeks('6/1/2014', '6/8/2014') | 1 |
|
| TotalWeeks('6/1/2014', '6/2/2014') | 0.142857143 |
TotalDays | Returns the total (fractional) number of days between two dates. | TotalDays('6/1/2014', '6/8/2014') | 7 |
|
| TotalDays('6/1/2014', '6/2/2014') | 1 |
|
| TotalDays('6/1/2014', '6/2/2014 12 PM') | 1.5 |
TotalHours | Returns the total (fractional) number of hours between two dates. | TotalHours('6/1/2014 1 PM', 6/2/2014 11 AM') | 22 |
|
| TotalHours('6/1/2014 1 PM', 6/1/2014 1:30 PM') | 0.5 |
TotalMinutes | Returns the total (fractional) number of minutes between two dates. | TotalMinutes('6/1/2014 6:01 AM', '6/1/2014 8:30 AM') | 149 |
TotalSeconds | Returns the total (fractional) number of seconds between two dates. | TotalSeconds('6/1/2014 6:01 AM', '6/1/2014 8:30 AM') | 8940 |
DiffYears | Returns the integer difference in calendar years of two dates. | DiffYears('1/1/2014', '7/1/2014') | 0 |
|
| DiffYears('3/1/2014', '1/1/2015') | 1 |
DiffMonths | Returns the integer difference in calendar months of two dates. | DiffMonths('3/1/2014', '5/15/2014') | 2 |
|
| DiffMonths('3/1/2014', '1/20/2014') | -2 |
DiffWeeks | Returns the integer difference in calendar weeks of two dates. Optionally accepts a string indicating the first day of the week. D | DiffWeeks('6/1/2014', '6/5/2014') | 0 |
|
| DiffWeeks('6/1/2014', '6/8/2014') | 1 |
|
| DiffWeeks('6/1/2014', '6/5/2014', 'Monday') | 1 |
DiffDays | Returns the integer difference in calendar days of two dates. | DiffDays('6/1/2014', '6/5/2014') | 4 |
|
| DiffDays('6/1/2014 1 AM', '6/1/2014 11: 30 PM') | 0 |
DiffHours | Returns the integer difference in calendar hours of two dates | DiffHours('6/1/2014 9 AM', '6/1/2014 2: 30 PM') | 5 |
DiffMinutes | Returns the integer difference in calendar minutes of two dates. | DiffMinutes('6/1/2014 9 AM', '6/1/2014 2: 30 PM') | 330 |
DiffSeconds | Returns the integer difference in calendar seconds of two dates. | DiffSeconds('6/1/2014 9 AM', '6/1/2014 2: 30 PM') | 19800 |
AddYears | Add the integer number of years to the given date | AddYears('3/1/14',1) | 3/1/2015 |
AddMonths | Add the integer number of months to the given date. | AddMonths('3/1/14',1) | 4/1/2014 |
AddWeeks | Add the integer number of weeks to the given date. | AddWeeks('3/1/14' 2) | 3/15/2015 |
AddDays | Adds the fractional number of days to the given date. | AddDays('3/1/14' 3) | 3/4/2014 |
|
| AddDays('3/1/14' 1.5) | 3/2/2014 12pm |
AddHours | Adds the fractional number of hours to the given date. | AddHours('2/2/14 3 PM', 2.5) | 2/2/2014 5:30pm |
|
| AddHours('2/2/14 3 PM', 50) | 2/4/2014 5pm |
AddMinutes | Adds the fractional number of minutes to the given date. | AddMinutes('4/1/2014 4:45 PM', 30) | 4/1/2014 5:15pm |
AddSeconds | Adds the fractional number of seconds to the given date. | AddSeconds('4/1/2014 3:30 PM, 60) | 4/1/2014 3:31pm |
Year | Returns the year of given date. | Year('6/1/2014 1:23:45 PM') | 2014 |
Month | Returns the month of given date. | Year('6/1/2014 1:23:45 PM') | 6 |
Week | Returns the week of the year, based on Sunday start and the first week having at least 4 days in year. | Week('6/1/2014') | 23 |
Day | Returns the day of given date. | Year('6/1/2014 1:23:45 PM') | 7 |
Hour | Returns the hour of given date. | Year('6/1/2014 1:23:45 PM') | 13 |
Minute | Returns the minute of given date. | Year('6/1/2014 1:23:45 PM') | 23 |
Second | Returns the second of given date. | Year('6/1/2014 1:23:45 PM') | 45 |
WeekDay | Returns the an integer for the week day (1-7), Sunday - Saturday | WeekDay('6/8/2014') | 1 |
Now | Returns the current timestamp for local timezone. | Now() | 7/1/2014 12:30 |
UtcNow | Returns the current timestamp for utc timezone. | UtcNow() | 7/1/2014 18:30 |
IsEmpty | Returns true if value is null or zero length, false otherwise. | IsEmpty('') | TRUE |