E Expression Editor Reference

Aggregate At

AGGREGATE(sales AT month, region)

This function aggregates columns based on the level or levels you specify.

measure is the name of a measure column. level is the level at which you want to aggregate. You can optionally specify more than one level. You cannot specify a level from a dimension that contains levels that are being used as the measure level for the measure you specified in the first argument. For example, you cannot write the function as AGGREGATE(yearly_sales AT month) because "month" is from the same time dimension that is being used as the measure level for "yearly_sales".

AGGREGATE(measure AT level [, level1, levelN])

Avg

Avg(Sales)

Calculates the average (mean) of a numeric set of values.

Avg(expr)

AvgDistinct

Calculates the average (mean) of all distinct values of an expression.

Avg(DISTINCT expr)

Bin

BIN(revenue BY productid, year WHERE productid > 2 INTO 4 BINS RETURNING RANGE_LOW)

The BIN function classifies a given numeric expression into a specified number of equal width buckets. The function can return either the bin number or one of the two end points of the bin interval. numeric_expr is the measure or numeric attribute to bin BY grain_expr1,…, grain_exprN is a list of expressions that define the grain at which the numeric_expr will be calculated. BY is required for measure expressions and is optional for attribute expressions. WHERE a filter to apply to the numeric_expr before the numeric values are assigned to bins INTO number_of_bins BINS is the number of bins to return BETWEEN min_value AND max_value is the min and max values used for the end points of the outermost bins RETURNING NUMBER indicates that the return value should be the bin number (1, 2, 3, 4, etc.). This is the default. RETURNING RANGE_LOW indicates the lower value of the bin interval RETURNING RANGE_HIGH indicates the higher value of the bin interval

BIN(numeric_expr [BY grain_expr1, ..., grain_exprN] [WHERE condition] INTO number_of_bins BINS [BETWEEN min_value AND max_value] [RETURNING {NUMBER | RANGE_LOW | RANGE_HIGH}])

BottomN

Ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numerical value.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

BottomN(expr, integer)

Count

Count(Products)

Determines the number of items with a non-null value.

COUNT(expr)

CountDistinct

Adds distinct processing to the Count function.

expr is any expression.

COUNT(DISTINCT expr)

Count*

SELECT COUNT(*) FROM Facts

Counts the number of rows.

COUNT(*)

First

First(Sales)

Selects the first non-null returned value of the expression argument. The First function operates at the most detailed level specified in your explicitly defined dimension.

First([NumericExpression)]

Last

Last(Sales)

Selects the last non-null returned value of the expression.

Last([NumericExpression)]

Mavg

Calculates a moving average (mean) for the last n rows of data in the result set, inclusive of the current row.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the average of the last n rows of data.

MAVG(expr, integer)

Max

Max(Revenue)

Calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.

MAX(expr)

Median

Median(Sales)

Calculates the median (middle) value of the rows satisfying the numeric expression argument. When there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.

MEDIAN(expr)

Min

Min(Revenue)

Calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.

MIN(expr)

NTile

Determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. NTile with numTiles=100 returns what is commonly called the "percentile" (with numbers ranging from 1 to 100, with 100 representing the high end of the sort).

expr is any expression that evaluates to a numerical value. numTiles is a positive, nonnull integer that represents the number of tiles.

NTILE(expr, numTiles)

Percentile

Calculates a percentile rank for each value satisfying the numeric expression argument. The percentile rank ranges are between 0 (0th percentile) to 1 (100th percentile).

expr is any expression that evaluates to a numerical value.

PERCENTILE(expr)

Rank

RANK(chronological_key, null, year_key_columns)

Calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

expr is any expression that evaluates to a numerical value.

RANK(expr)

StdDev

StdDev(Sales) StdDev(DISTINCT Sales)

Returns the standard deviation for a set of values. The return type is always a double.

STDDEV(expr)

StdDev_Pop

StdDev_Pop(Sales) StdDev_Pop(DISTINCT Sales)

Returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.

StdDev_Pop([NumericExpression])

Sum

Sum(Revenue)

Calculates the sum obtained by adding up all values satisfying the numeric expression argument.

SUM(expr)

SumDistinct

Calculates the sum obtained by adding all of the distinct values satisfying the numeric expression argument.

expr is any expression that evaluates to a numerical value.

SUM(DISTINCT expr)

TopN

Ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numerical value.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

TOPN(expr, integer)

Current_Date

Current_Date

Returns the current date.

The date is determined by the system in which the Oracle BI is running.

CURRENT_DATE

Current_Time

Current_Time(3)

Returns the current time to the specified number of digits of precision, for example: HH:MM:SS.SSS

If no argument is specified, the function returns the default precision.

CURRENT_TIME(expr)

Current_TimeStamp

Current_TimeStamp(3)

Returns the current date/timestamp to the specified number of digits of precision.

CURRENT_TIMESTAMP(expr)

DayName

DayName(Order_Date)

Returns the name of the day of the week for a specified date expression.

DAYNAME(expr)

DayOfMonth

DayOfMonth(Order_Date)

Returns the number corresponding to the day of the month for a specified date expression.

DAYOFMONTH(expr)

DayOfWeek

DayOfWeek(Order_Date)

Returns a number between 1 and 7 corresponding to the day of the week for a specified date expression. For example, 1 always corresponds to Sunday, 2 corresponds to Monday, and so on through to Saturday which returns 7.

DAYOFWEEK(expr)

DayOfYear

DayOfYear(Order_Date)

Returns the number (between 1 and 366) corresponding to the day of the year for a specified date expression.

DAYOFYEAR(expr)

Day_Of_Quarter

Day_Of_Quarter(Order_Date)

Returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date expression.

DAY_OF_QUARTER(expr)

Hour

Hour(Order_Time)

Returns a number (between 0 and 23) corresponding to the hour for a specified time expression. For example, 0 corresponds to 12 a.m. and 23 corresponds to 11 p.m.

HOUR(expr)

Minute

Minute(Order_Time)

Returns a number (between 0 and 59) corresponding to the minute for a specified time expression.

MINUTE(expr)

Month

Month(Order_Time)

Returns the number (between 1 and 12) corresponding to the month for a specified date expression.

MONTH(expr)

MonthName

MonthName(Order_Time)

Returns the name of the month for a specified date expression.

MONTHNAME(expr)

Month_Of_Quarter

Month_Of_Quarter(Order_Date)

Returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date expression.

MONTH_OF_QUARTER(expr)

Now

Now()

Returns the current timestamp. The Now function is equivalent to the Current_Timestamp function.

NOW()

Quarter_Of_Year

Quarter_Of_Year(Order_Date)

Returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date expression.

QUARTER_OF_YEAR(expr)

Second

Second(Order_Time)

Returns the number (between 0 and 59) corresponding to the seconds for a specified time expression.

SECOND(expr)

TimeStampAdd

TimeStampAdd(SQL_TSI_MONTH, 12,Time."Order Date")

Adds a specified number of intervals to a timestamp, and returns a single timestamp.

Interval options are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR

TIMESTAMPADD(interval, expr, timestamp)

TimeStampDiff

TimeStampDiff(SQL_TSI_MONTH, Time."Order Date",CURRENT_DATE)

Returns the total number of specified intervals between two timestamps.

Use the same intervals as TimeStampAdd.

TIMESTAMPDIFF(interval, expr, timestamp2)

Week_Of_Quarter

Week_Of_Quarter(Order_Date)

Returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date expression.

WEEK_OF_QUARTER(expr)

Week_Of_Year

Week_Of_Year(Order_Date)

Returns a number (between 1 and 53) corresponding to the week of the year for the specified date expression.

WEEK_OF_YEAR(expr)

Year

Year(Order_Date)

Returns the year for the specified date expression.

YEAR(expr)

Cast

Cast(hiredate AS CHAR(40)) FROM employee

Changes the data type of an expression or a null literal to another data type. For example, you can cast a customer_name (a data type of Char or Varchar) or birthdate (a datetime literal).

Use Cast to change to a Date data type.

Don’t use ToDate.

CAST(expr AS type)

IfNull

IfNull(Sales, 0)

Tests if an expression evaluates to a null value, and if it does, assigns the specified value to the expression.

IFNULL(expr, value)

IndexCol

SELECT IndexCol(VALUEOF (NQ_SESSION.GEOGRAPHY_LEVEL), Country, State, City), Revenue FROM Sales

Uses external information to return the appropriate column for the signed-in user to see.

IndexCol([integer literal], [expr1] [, [expr2], ?-])

NullIf

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name, "Old Job ID";

Compares two expressions. If they’re equal, then the function returns null. If they’re not equal, then the function returns the first expression. You can’t specify the literal NULL for the first expression.

NullIf([expression], [expression])

To_DateTime

SELECT To_DateTime ('2009-03-0301:01:00', 'yyyy-mm-dd hh:mi:ss') FROM sales

Converts string literals of dateTime format to a DateTime data type.

To_DateTime([expression], [literal])

BottomN

BottomN(Sales, 10)

Returns the n lowest values of expression, ranked from lowest to highest.

BottomN([NumericExpression], [integer])

Filter

Filter(Sales USING Product = 'widget')

Computes the expression using the given preaggregate filter.

FILTER(measure USING filter_expr)

Mavg

Mavg(Sales, 10)

Calculates a moving average (mean) for the last n rows of data in the result set, inclusive of the current row.

Mavg([NumericExpression], [integer])

Msum

SELECT Month, Revenue, Msum(Revenue, 3) as 3_MO_SUM FROM Sales

Calculates a moving sum for the last n rows of data, inclusive of the current row.

The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data, and so on. When the nth row is reached, the sum is calculated based on the last n rows of data.

Msum([NumericExpression], [integer])

NTile

Ntile(Sales, 100)

Determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. The example shows a range from 1 to 100, with the lowest sale = 1 and the highest sale = 100.

NTile([NumericExpresssion], [integer])

Percentile

Percentile(Sales)

Calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.

Percentile([NumericExpression])

Rank

Rank(Sales)

Calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

Rank([NumericExpression])

Rcount

SELECT month, profit, Rcount(profit) FROM sales WHERE profit > 200

Takes a set of records as input and counts the number of records encountered so far.

Rcount([NumericExpression])

Rmax

SELECT month, profit, Rmax(profit) FROM sales

Takes a set of records as input and shows the maximum value based on records encountered so far. The specified data type must be one that can be ordered.

Rmax([NumericExpression])

Rmin

SELECT month, profit, Rmin(profit) FROM sales

Takes a set of records as input and shows the minimum value based on records encountered so far. The specified data type must be one that can be ordered.

Rmin([NumericExpression])

Rsum

SELECT month, revenue, Rsum(revenue) as RUNNING_SUM FROM sales

Calculates a running sum based on records encountered so far.

The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data, and so on.

Rsum([NumericExpression])

TopN

TopN(Sales, 10)

Returns the n highest values of expression, ranked from highest to lowest.

TopN([NumericExpression], [integer])

Evaluate

SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees

Passes the specified database function with optional referenced columns as parameters to the database for evaluation.

Evaluate([string expression], [comma separated expressions])

Evaluate_Aggr

EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)

Passes the specified database function with optional referenced columns as parameters to the database for evaluation. This function is intended for aggregate functions with a GROUP BY clause.

EVALUATE_AGGR('db_agg_function(%1...%N)' [AS datatype] [, column1, columnN])

Abs

Abs(Profit)

Calculates the absolute value of a numeric expression.

expr is any expression that evaluates to a numerical value.

ABS(expr)

Acos

Acos(1)

Calculates the arc cosine of a numeric expression.

expr is any expression that evaluates to a numerical value.

ACOS(expr)

Asin

Asin(1)

Calculates the arc sine of a numeric expression.

expr is any expression that evaluates to a numerical value.

ASIN(expr)

Atan

Atan(1)

expr is any expression that evaluates to a numerical value.

Calculates the arc tangent of a numeric expression.

ATAN(expr)

Atan2

Atan2(1, 2)

Calculates the arc tangent of y/x, where y is the first numeric expression and x is the second numeric expression.

ATAN2(expr1, expr2)

Ceiling

Ceiling(Profit)

Rounds a non-integer numeric expression to the next highest integer. If the numeric expression evaluates to an integer, the CEILING function returns that integer.

CEILING(expr)

Cos

Cos(1)

Calculates the cosine of a numeric expression.

expr is any expression that evaluates to a numerical value.

COS(expr)

Cot

Cot(1)

Calculates the cotangent of a numeric expression.

expr is any expression that evaluates to a numerical value.

COT(expr)

Degrees

Degrees(1)

Converts an expression from radians to degrees.

expr is any expression that evaluates to a numerical value.

DEGREES(expr)

Exp

Exp(4)

Sends the value to the power specified. Calculates e raised to the n-th power, where e is the base of the natural logarithm.

EXP(expr)

ExtractBit

Int ExtractBit(1, 5)

Retrieves a bit at a particular position in an integer. It returns an integer of either 0 or 1 corresponding to the position of the bit.

ExtractBit([Source Number], [Digits])

Floor

Floor(Profit)

Rounds a non-integer numeric expression to the next lowest integer. If the numeric expression evaluates to an integer, the FLOOR function returns that integer.

Log

Log(1)

Calculates the natural logarithm of an expression.

expr is any expression that evaluates to a numerical value.

LOG(expr)

Log10

Log10(1)

Calculates the base 10 logarithm of an expression.

expr is any expression that evaluates to a numerical value.

LOG10(expr)

Mod

Mod(10, 3)

Divides the first numeric expression by the second numeric expression and returns the remainder portion of the quotient.

MOD(expr1, expr2)

Pi

Pi()

Returns the constant value of pi.

PI()

Power

Power(Profit, 2)

Takes the first numeric expression and raises it to the power specified in the second numeric expression.

POWER(expr1, expr2)

Radians

Radians(30)

Converts an expression from degrees to radians.

expr is any expression that evaluates to a numerical value.

RADIANS(expr)

Rand

Rand()

Returns a pseudo-random number between 0 and 1.

RAND()

RandFromSeed

Rand(2)

Returns a pseudo-random number based on a seed value. For a given seed value, the same set of random numbers are generated.

RAND(expr)

Round

Round(2.166000, 2)

Rounds a numeric expression to n digits of precision.

expr is any expression that evaluates to a numerical value.

integer is any positive integer that represents the number of digits of precision.

ROUND(expr, integer)

Sign

Sign(Profit)

This function returns the following:

• 1 if the numeric expression evaluates to a positive number

• -1 if the numeric expression evaluates to a negative number

• 0 if the numeric expression evaluates to zero

SIGN(expr)

Sin

Sin(1)

Calculates the sine of a numeric expression.

SIN(expr)

Sqrt

Sqrt(7)

Calculates the square root of the numeric expression argument. The numeric expression must evaluate to a nonnegative number.

SQRT(expr)

Tan

Tan(1)

Calculates the tangent of a numeric expression.

expr is any expression that evaluates to a numerical value.

TAN(expr)

Truncate

Truncate(45.12345, 2)

Truncates a decimal number to return a specified number of places from the decimal point.

expr is any expression that evaluates to a numerical value.

integer is any positive integer that represents the number of characters to the right of the decimal place to return.

TRUNCATE(expr, integer)

Mavg

Calculates a moving average (mean) for the last n rows of data in the result set, inclusive of the current row.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the average of the last n rows of data.

MAVG (expr, integer)

Msum

select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area

This function calculates a moving sum for the last n rows of data, inclusive of the current row.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the sum of the last n rows of data.

MSUM(expr, integer)

Rsum

SELECT month, revenue, RSUM(revenue) as RUNNING_SUM from sales_subject_area

This function calculates a running sum based on records encountered so far.

expr is any expression that evaluates to a numerical value.

RSUM(expr)

Rcount

select month, profit, RCOUNT(profit) from sales_subject_area where profit > 200

This function takes a set of records as input and counts the number of records encountered so far.

expr is an expression of any datatype.

RCOUNT(expr)

Rmax

SELECT month, profit,RMAX(profit) from sales_subject_area

This function takes a set of records as input and shows the maximum value based on records encountered so far.

expr is an expression of any datatype.

RMAX(expr)

Rmin

select month, profit,RMIN(profit) from sales_subject_area

This function takes a set of records as input and shows the minimum value based on records encountered so far.

expr is an expression of any datatype.

RMIN(expr)

Ascii

Ascii('a')

Converts a single character string to its corresponding ASCII code, between 0 and 255. If the character expression evaluates to multiple characters, the ASCII code corresponding to the first character in the expression is returned.

expr is any expression that evaluates to a character string.

ASCII(expr)

Bit_Length

Bit_Length('abcdef')

Returns the length, in bits, of a specified string. Each Unicode character is 2 bytes in length (equal to 16 bits).

expr is any expression that evaluates to a character string.

BIT_LENGTH(expr)

Char

Char(35)

Converts a numeric value between 0 and 255 to the character value corresponding to the ASCII code.

expr is any expression that evaluates to a numerical value between 0 and 255.

CHAR(expr)

Char_Length

Char_Length(Customer_Name)

Returns the length, in number of characters, of a specified string. Leading and trailing blanks aren’t counted in the length of the string.

expr is any expression that evaluates to a character string.

CHAR_LENGTH(expr)

Concat

SELECT DISTINCT Concat ('abc', 'def') FROM employee

Concatenates two character strings.

exprs are expressions that evaluate to character strings, separated by commas.

CONCAT(expr1, expr2)

Insert

SELECT Insert('123456', 2, 3, 'abcd') FROM table

Inserts a specified character string into a specified location in another character string.

expr1 is any expression that evaluates to a character string. Identifies the target character string.

integer1 is any positive integer that represents the number of characters from the beginning of the target string where the second string is to be inserted.

integer2 is any positive integer that represents the number of characters in the target string to be replaced by the second string.

expr2 is any expression that evaluates to a character string. Identifies the character string to be inserted into the target string.

INSERT(expr1, integer1, integer2, expr2)

Left

SELECT Left('123456', 3) FROM table

Returns a specified number of characters from the left of a string.

expr is any expression that evaluates to a character string

integer is any positive integer that represents the number of characters from the left of the string to return.

LEFT(expr, integer)

Length

Length(Customer_Name)

Returns the length, in number of characters, of a specified string. The length is returned excluding any trailing blank characters.

expr is any expression that evaluates to a character string.

LENGTH(expr)

Locate

Locate('d' 'abcdef')

Returns the numeric position of a character string in another character string. If the character string isn’t found in the string being searched, the function returns a value of 0.

expr1 is any expression that evaluates to a character string. Identifies the string for which to search.

expr2 is any expression that evaluates to a character string.

Identifies the string to be searched.

LOCATE(expr1, expr2)

LocateN

Locate('d' 'abcdef', 3)

Like Locate, returns the numeric position of a character string in another character string. LocateN includes an integer argument that enables you to specify a starting position to begin the search.

expr1 is any expression that evaluates to a character string. Identifies the string for which to search.

expr2 is any expression that evaluates to a character string. Identifies the string to be searched. intergeris any positive (nonzero) integer that represents the starting position to begin to look for the character string.

LOCATEN(expr1, expr2, integer)

Lower

Lower(Customer_Name)

Converts a character string to lowercase.

expr is any expression that evaluates to a character string.

LOWER(expr)

Octet_Length

Octet_Length('abcdef')

Returns the number of bytes of a specified string.

expr is any expression that evaluates to a character string.

OCTET_LENGTH(expr)

Position

Position('d', 'abcdef')

Returns the numeric position of strExpr1 in a character expression. If strExpr1 isn’t found, the function returns 0.

expr1 is any expression that evaluates to a character string. Identifies the string to search for in the target string.

expr2 is any expression that evaluates to a character string. Identifies the target string to be searched.

POSITION(expr1 IN expr2)

Repeat

Repeat('abc', 4)

Repeats a specified expression n times.

expr is any expression that evaluates to a character string

integer is any positive integer that represents the number of times to repeat the character string.

REPEAT(expr, integer)

Replace

Replace('abcd1234', '123', 'zz')

Replaces one or more characters from a specified character expression with one or more other characters.

expr1 is any expression that evaluates to a character string. This is the string in which characters are to be replaced.

expr2 is any expression that evaluates to a character string. This second string identifies the characters from the first string that are to be replaced.

expr3 is any expression that evaluates to a character string. This third string specifies the characters to substitute into the first string.

REPLACE(expr1, expr2, expr3)

Right

SELECT Right('123456', 3) FROM table

Returns a specified number of characters from the right of a string.

expr is any expression that evaluates to a character string.

integer is any positive integer that represents the number of characters from the right of the string to return

RIGHT(expr, integer)

Space

Space(2)

Inserts blank spaces.

integer is any positive integer that indicates the number of spaces to insert.

SPACE(expr)

Substring

Substring('abcdef' FROM 2)

Creates a new string starting from a fixed number of characters into the original string.

expr is any expression that evaluates to a character string.

startPos is any positive integer that represents the number of characters from the start of the left side of the string where the result is to begin.

Substring([SourceString] FROM [StartPostition])

SubstringN

Substring('abcdef' FROM 2 FOR 3)

Like Substring, creates a new string starting from a fixed number of characters into the original string.

SubstringN includes an integer argument that enables you to specify the length of the new string, in number of characters.

xpr is any expression that evaluates to a character string.

startPos is any positive integer that represents the number of characters from the start of the left side of the string where the result is to begin.

SUBSTRING(expr FROM startPos FOR length)

TrimBoth

Trim(BOTH '_' FROM '_abcdef_')

Strips specified leading and trailing characters from a character string.

char is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

expr is any expression that evaluates to a character string.

TRIM(BOTH char FROM expr)

TrimLeading

Trim(LEADING '_' FROM '_abcdef')

Strips specified leading characters from a character string.

char is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

expr is any expression that evaluates to a character string.

TRIM(LEADING char FROM expr)

TrimTrailing

Trim(TRAILING '_' FROM 'abcdef_')

Strips specified trailing characters from a character string.

char is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

expr is any expression that evaluates to a character string.

TRIM(TRAILING char FROM expr)

Upper

Upper(Customer_Name)

Converts a character string to uppercase.

Expr is any expression that evaluates to a character string.

UPPER(expr)

Ago

SELECT Year_ID, Ago(sales, year, 1)

Calculates the aggregated value of a measure from the current time to a specified time period in the past. For example, AGO can produce sales for every month of the current quarter and the corresponding quarter-ago sales.

AGO(expr, time_level, offset)

Periodrolling

SELECT Month_ID, Periodrolling (monthly_sales, -1, 1)

Computes the aggregate of a measure over the period starting x units of time and ending y units of time from the current time. For example, PERIODROLLING can compute sales for a period that starts at a quarter before and ends at a quarter after the current quarter.

measure is the name of a measure column.

offset from the current time.

y specifies the number of time units over which the function will compute.

hierarchy is an optional argument that specifies the name of a hierarchy in a time dimension, such as yr, mon, day, that you want to use to compute the time window.

PERIODROLLING(measure, x [,y])

ToDate

SELECT Year_ID, Month_ID, ToDate (sales, year)

Aggregates a measure from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.

expr is an expression that references at least one measure column.

time_level is the type of time period, such as quarter, month, or year.

TODATE(expr, time_level)

Forecast

FORECAST(numeric_expr, ([series]), output_column_name, options, [runtime_binded_options])

Creates a time-series model of the specified measure over the series using either Exponential Smoothing or ARMIA and outputs a forecast for a set of periods as specified by numPeriods.

numeric _expr represents the measure to forecast.

series is the time grain at which the forecast model is built. It is a list of one or more time dimension columns. If series is omitted, the time grain is determined from the query.

output_column_name is the output column. The valid values are 'forecast', 'low', 'high', 'predictionInterval' .

options is a string list of name=value pairs separated by ';'. The value can include %1 ... %N, which can be specified using runtime_binded_options.

runtime_binded_options is an optional comma separated list of runtime binded colums or literal expressions.

FORECAST(numeric_expr, ([series]), output_column_name, options, [runtime_binded_options])