When using the ToChar function, you must use the .Net format strings. The following are some examples:
| Format | Example |
|---|---|
| MM/dd/yyyy | 08/26/2011 |
| dddd, dd MMMM yyyy | Tuesday, 26 August 2011 |
| dddd, dd MMMM yyyy h:mm tt | Tuesday, 26 August 2011 6:30 AM |
| MM/dd/yyyy h:mm tt | 08/26/2011 6:30 AM |
| dddd, dd MMMM yyyy HH:mm:ss | Tuesday, 26 August 2011 06:30:07 |
| MM/dd/yyyy HH:mm:ss | 08/26/2011 06:30:07 |
| #### | 1234 |
| #.## | 1.23 |
| 0.00 | 1.23 |
| #,# | 1,234,456 |
| (###) ### - #### | (123) 456 - 7890 |
Notes:
Math functions return a numeric value based on input values supplied as arguments. Review the following for additional information:
| Abs(number) | Returns the absolute value of a number. |
| Atan(number) | Returns the arc tangent of a number as a numeric value expressed in radians. |
| Ceiling(number) | Returns the first integer that is greater than or equal to the specified number. |
| Cos(number) | Returns a number in radians specifying the cosine of an angle. |
| Cosh(number) |
Returns the hyperbolic cosine of a specified number. Notes:
|
| Count(field) |
Returns the number of rows retrieved from the field. Note: This function counts only those records in which the field is not Null. |
| Exp(number) |
Returns a number specifying e. The value of e is approximately 2.718282. Tip: This function is useful to see a relationship in which a percentage increase or decrease in the independent variable has the same proportional change in the dependent variable. |
| Floor(number) | Returns the first integer that is less than or equal to the specified number. |
| Hex(number) | Returns hexadecimal representation of the number. |
| Max(num1, num2) | Returns the maximum of two expressions. |
| Min (num1, num 2) | Returns the minimum of two expressions. |
| Power(number, n) | Calculates the number of times an expression is multiplied by itself. |
| Round(number) | Returns a similar value of a number while reducing the number of digits. |
| Sign(number) | Returns the sign of the number (positive values are 1, negative values are -1, and 0 is 0). |
| Sin(number) | Returns a number in radians specifying the sine of an angle. |
| Sinh(number) | Returns the hyperbolic sine of a number. |
| Sqrt(number) | Returns the square root of a number. |
| Tan(number) | Returns a number in radians specifying the tangent of an angle. |
| Tanh(number) | Returns the hyperbolic tangent of a number. |
| Trunc(number, precision) | Returns a number truncated to specified precision. |
You can use string functions to manipulate, change, or edit the contents of a string. Review the following for additional information:
| Nvl(field,valueWhenNULL) | Returns a substitute value if a value is null where field represents the position of the first argument in succeeding arguments, and valueWhenNull represents the value to replace the null values. |
| If(expression, value, default) | Returns a default value for a row where expression evaluates to the default value, value represents the default value, and default assigns the default value. |
| Ascii(string) | Returns the numeric value of the first left character in a string where string represents the string to convert. |
| Concat(string1, string2) | Joins one or more strings to the current string and returns the result where string1 represents the first string to join and string2 represents the second string to join. |
| Initcap(string) | Changes the first letter of a string to uppercase where string represents the string to manipulate. |
| Instr(str1, str2, startPos,occurance) | Determines if one string contains another and returns the position of the first occurrence of one string within another where str1 represents the string to be searched, str2 represents the string searched for in str1, and startPos represents where the search is to begin in str1. |
| Length(string) | Returns the length of a string where string represents the string to manipulate. |
| Lower(string) | Returns the entire string in lowercase where string represents the string to manipulate. |
| Ltrim(str1,charsToRemove) | Removes the leftmost leading characters in a string where str1 represents the string to manipulate and charsToRemove represent characters in the trim list. |
| RemovePrefix(str1,str2) |
Removes the leftmost leading characters in a string where str1 represents the string to manipulate and str2 represents the string to remove. If str2 is not found on the leading characters, str1 is returned. For example, RemovePrefix('IntMyVariable','Int') returns: MyVariable. |
| Replace(str1,str2,str3) | Replaces occurrences of a string where str1, str2, and str3 represent the strings to manipulate. |
| Rtrim(str1,charsToRemove) | Removes the rightmost trailing characters in a string where str1 represents the string to manipulate and charsToRemove represent characters in the trim list. |
| RemoveSuffix(str1,str2) |
Removes the rightmost trailing characters in a string where str1 represents the string to manipulate and str2 represents the string to remove. If str2 is not found on the trailing characters, str1 is returned. For example, RemoveSuffix('MyVariableEnd','End') returns: MyVariable. |
| Substr(str1, start, length) | Returns the substring of a string where str1 represents the string to manipulate, start represents the beginning of the string to manipulate, and length represents the length of the string to manipulate. |
| Upper(String) | Returns the entire string in uppercase where String represents the string to manipulate. |
You can use statistical functions to collect statistical information about column data. Review the following for additional information:
| Median(number) | Calculates the middle value in a column of numbers arranged in value order where number represents the column of numbers. If a column contains an even number of rows, the mean of the two middle values are calculated. |
| Median(number, break) | Calculates the middle value in a column of numbers arranged in value order where number represents the column of numbers, and break represents an optional break column. If a column contains an even number of rows, the mean of the two middle values is calculated. |
| Mode(number) | Calculates the value that occurs most frequently in a column of numbers where numbers represents the column of numbers. |
| Mode(number, break) |
Calculates the value that occurs most frequently in a column of numbers where numbers represents the column of numbers, and break represents an optional break column. Note: Mode includes values of zero (0) and ignores null values. If there are no duplicate values, the value of the first cell is returned. |
| Percentile(numbers, n) | Measures the nth percentile of the total frequency of values in a column of numbers where numbers represents the column of numbers, and n represents the percentile value zero to one. |
| Percentile(numbers, n, break) |
Measures the nth percentile of the total frequency of values in a column of numbers where numbers represents the column of numbers, n represents the percentile value zero to one, and break represents an optional break column. Tip: You can also measure quartile values.
|
| Rank(numbers) | Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers. |
| Rank(numbers, break) |
Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers, and break represents an optional break column. Note: Duplicate numbers have the same ranking and affect the ranking of subsequent numbers. |
| RankAsc(numbers) | Calculates the sequence of numbers in a column in ascending order where numbers represents the column of numbers. |
| RankAsc(numbers, break) |
Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers, and break represents an optional break column. Note: Duplicate numbers have the same ranking and affect the ranking of subsequent numbers. |
| GeometricMean(number) | Returns an average calculated by multiplying all numbers in a set and taking the nth root of the total where number is the number of numbers. |
| GeometricMean(number, break) |
Returns an average calculated by multiplying all numbers in a set and taking the nth root of the total where number is the number of numbers, and break represents an optional break column. Note: The geometric mean formula might produce large numbers. |
| StdDev(number) |
Calculates the dispersion of values in a sampling of the data set from its average value (mean) where number represents the column of numbers. The more spread out the data, the higher the deviation. Tip: StdDev uses a sampling of the data to calculate standard deviation. Use StdDevp for the entire data set. Notes:
|
| StdDev(number, break) |
Calculates the dispersion of values in a sampling of the data set from its average value (mean) where numbers represents the column of numbers, and break represents an optional break column. Tip: StdDev uses a sampling of the data to calculate standard deviation. Use StdDevp for the entire data set. Notes:
|
| StdDevp(number) |
Calculates the dispersion of values in the entire data set from its average value (mean) where number represents the column of numbers. The more spread out the data, the higher the deviation. Tip: StdDevp uses the entire data set to calculate standard deviation. Use StdDev for a sampling of the data set. Note: The biased or n method is used to calculate standard deviation. |
| StdDevp(number, break) |
Calculates the dispersion of values in the entire data set from its average value (mean) where number represents the column of numbers, and break represents an optional break column. The more spread out the data, the higher the deviation. Tip: StdDevp uses the entire data set to calculate standard deviation. Use StdDev for a sampling of the data set. Note: The biased or n method is used to calculate standard deviation. |
| Var(number) | Estimates variability drawn randomly from a sampling of data where number represents the column of numbers. |
| Var(number, break) |
Estimates variability drawn randomly from a sampling of data where number represents the column of numbers, and break represents an optional break column. Tip: Var uses a sampling of the data to estimate variability. Use Varp for the entire data set. |
| Varp (number) | Estimates variability using the entire data set where number represents the column of numbers. |
| Varp(number, break) |
Estimates variability using the entire data set where number represents the column of numbers, and break represents an optional break column. Tip: Varp uses the entire data set to estimate variability. Use Var for a sampling of the data set. |
A moving average is used to indicate a trend. It detects the start of a trend and follows its progress, including reporting its reversal. Review the following for additional information:
| Simple Moving Averages | Calculate the mean of a window for every case in the data set. It sums all cases in the window and divides the result by the number of cases in the window. Each data value is given equal weight in the calculation. . |
|
MovingAverage(number, window_size) |
Where number represents the number of data points, and window_size represents a specific number of periods |
|
MovingAverage(number, window_size, break_col) |
Where number represents the number of data points, window_size represents a specific number of periods, and break_col represents an optional break column. |
| Weighted Moving Averages |
Include multiplying factors to assign different weight to values from different periods. Usually, more recent a value, the higher it is weighted. To calculate a Weighted Moving Average
|
|
MovingAverageWeighted(number, window_size) |
Where number represents the number of data points, and window_size represents a specific number of periods. |
|
MovingAverageWeighted(number, window_size, break_col) |
Where number represents the number of data points, window_size represents a specific number of periods, and break_col represents an optional break column. |
| Exponential Moving Averages | Similar to Weighted Moving Averages, Exponential Moving Averages give more weight to recent data values, and all available data values are taken into account. Exponential Moving Averages are more accurate that other Moving Average calculations when original data values reveal rapid variability over time. |
|
MovingAverageExponential (number, window_size) |
Where number represents the number of data points, and window_size represents a specific number of periods. |
|
MovingAverageExponential (number, window_size, break_col) |
Where number represents the number of data points, window_size represents a specific number of periods, and break_col represents the column on which to perform the calculation. |
| Moving Diff | Moving Diff operates as a moving subtraction over a window and returns the difference between the current value and the first value in the window. Subtraction can only be performed on two numbers at a time. MovingDiff provides insight into a column’s recurring differences to alert the user to any abnormalities. This function does not calculate Weighted or Exponential averages. |
|
MovingDiff(number, window_size) |
Where number (required) represents the column containing the numeric value to subtract and window_size (optional) represents how many rows to use in the calculation. |
|
MovingDiff(number, window_size, break_col) |
Where number (required) represents the column containing the numeric value to subtract, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes:
|
| Moving Maximum | Returns the maximum value contained in a moving window. This function does not calculate Weighted or Exponential averages. |
|
MovingMax(number, window_size) |
Where number (required) represents the column containing the numeric value to calculate and window_size (optional) represents how many rows to use in the calculation. |
|
MovingMax(number, window_size, break_col |
Where number (required) represents the column containing the numeric value to calculate, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes:
|
| Moving Median | MovingMed returns the middle value contained in a moving window. If the window’s value is an even number, use the mean of the two middle values. This function does not calculate Weighted or Exponential averages. |
|
MovingMedian(number, window_size) |
Where number (required) represents the column containing the numeric value to calculate and window_size (optional) represents how many rows to use in the calculation. |
|
MovingMedian(number, window_size, break_col) |
Where number (required) represents the column containing the numeric value to calculate, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes:
|
| Moving Minimum | Returns the minimum value contained in a moving window. This function does not calculate Weighted or Exponential averages. |
|
MovingMin(number, window_size) |
Where number (required) represents the column containing the numeric value to calculate and window_size (optional) represents how many rows to use in the calculation. |
|
MovingMin(number, window_size, break_col) |
Where number (required) represents the column containing the numeric value to calculate, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes:
|
| Moving Sum | Returns the sum of values contained in a moving window. This function does not calculate Weighted or Exponential averages. |
|
MovingSum(number, window_size) |
Where number (required) represents the column containing the numeric value to calculate and window_size (optional) represents how many rows to use in the calculation. |
|
MovingSum(number, window_size, break_col) |
Where number (required) represents the column containing the numeric value to calculate, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes:
|
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
