1. Visualizing Data and Building Reports in Oracle Analytics Cloud
  2. Reference
  3. Expression Editor Reference
  4. Functions

Functions

There are various types of functions that you can use in expressions.

Topics:

Analytics Functions

Analytics functions allow you to explore data using models such as trendline and cluster.

Function Example Description Syntax

TRENDLINE

TRENDLINE(revenue, (calendar_year, calendar_quarter, calendar_month) BY (product), 'LINEAR', 'VALUE')

Oracle recommends that you apply a Trendline using the Add Statistics property when viewing a visualization. See Adjust Visualization Properties.

Fits a linear, polynomial, or exponential model, and returns the fitted values or model. The numeric_expr represents the Y value for the trend and the series (time columns) represent the X value.

TRENDLINE(numeric_expr, ([series]) BY ([partitionBy]), model_type, result_type)

CLUSTER

CLUSTER((product, company), (billed_quantity, revenue), 'clusterName', 'algorithm=k-means;numClusters=%1;maxIter=%2;useRandomSeed=FALSE;enablePartitioning=TRUE', 5, 10)

Collects a set of records into groups based on one or more input expressions using K-Means or Hierarchical Clustering.

CLUSTER((dimension_expr1 , ... dimension_exprN), (expr1, ... exprN), output_column_name, options, [runtime_binded_options])

OUTLIER

OUTLIER((product, company), (billed_quantity, revenue), 'isOutlier', 'algorithm=kmeans')

Classifies a record as Outlier based on one or more input expressions using K-Means or Hierarchical Clustering or Multi-Variate Outlier detection Algorithms.

OUTLIER((dimension_expr1 , ... dimension_exprN), (expr1, ... exprN), output_column_name, options, [runtime_binded_options])

REGR

REGR(revenue, (discount_amount), (product_type, brand), 'fitted', '')

Fits a linear model and returns the fitted values or model. This function can be used to fit a linear curve on two measures.

REGR(y_axis_measure_expr, (x_axis_expr), (category_expr1, ..., category_exprN), output_column_name, options, [runtime_binded_options])

Conversion Functions

Conversion functions convert a value from one form to another.

Function Example Description Syntax

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])

VALUEOF

SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")

References the value of a semantic model variable in a filter.

Use expr variables as arguments of the VALUEOF function. Refer to static semantic model variables by name.

VALUEOF(expr)

Date Extraction Functions

These functions calculate or round-down timestamp values to the nearest specified time period, such as hour, day, week, month, and quarter.

You can use the calculated timestamps to aggregate data using a different grain. For example, you might apply the EXTRACTDAY() function to sales order dates to calculate a timestamp for midnight on the day that orders occur, so that you can aggregate the data by day.

Function Example Description Syntax

Extract Day
EXTRACTDAY("Order Date")
  • 2/22/1967 3:02:01 AM returns 2/22/1967 12:00:00 AM.
  • 9/2/2022 10:38:21 AM returns 9/2/2022 12:00:00 AM.

Returns a timestamp for midnight (12 AM) on the day in which the input value occurs. For example, if the input timestamp is for 3:02:01 AM on February 22nd, the function returns the timestamp for 12:00:00 AM on February 22nd.

EXTRACTDAY(expr)

Extract Hour
EXTRACTHOUR("Order Date")
  • 2/22/1967 3:02:01 AM returns 2/22/1967 3:00:00 AM.
  • 6/17/1999 11:18:30 PM returns 6/17/1999 11:00:00 PM.

Returns a timestamp for the start of the hour in which the input value occurs. For example, if the input timestamp is for 11:18:30 PM, the function returns the timestamp for 11:00:00 PM.

EXTRACTHOUR (expr)

Extract Hour of Day
EXTRACTHOUROFDAY("Order Date")
  • 2014/09/24 10:58:00 returns 2000/01/01 10:00:00.
  • 2014/08/13 11:10:00 returns 2000/01/01 11:00:00

Returns a timestamp where the hour equals the hour of the input value with default values for year, month, day, minutes, and seconds.

EXTRACTHOUROFDAY(expr)

Extract Millisecond
EXTRACTMILLISECOND("Order Date")
  • 1997/01/07 15:32:02.150 returns 1997/01/07 15:32:02.150.
  • 1997/01/07 18:42:01.265 returns 1997/01/07 18:42:01.265.
Returns a timestamp containing milliseconds for the input value. For example, if the input timestamp is for 15:32:02.150, the function returns the timestamp for 15:32:02.150.

EXTRACTMILLISECOND(expr)

Extract Minute
EXTRACTMINUTE("Order Date")
  • 6/17/1999 11:18:00 PM returns 6/17/1999 11:18:00 PM.
  • 9/2/2022 10:38:21 AM returns 9/2/2022 10:38:00 AM.

Returns a timestamp for the start of the minute in which the input value occurs. For example, if the input timestamp is for 11:38:21 AM, the function returns the timestamp for 11:38:00 AM.

EXTRACTMINUTE (expr)

Extract Month
EXTRACTMONTH("Order Date")
  • 2/22/1967 3:02:01 AM returns 2/1/1967 12:00:00 AM.
  • 6/17/1999 11:18:00 PM returns 6/1/1999 12:00:00 AM.

Returns a timestamp for the first day in the month in which the input value occurs. For example, if the input timestamp is for February 22nd, the function returns the timestamp for February 1st.

EXTRACTMONTH(expr)

Extract Quarter
EXTRACTQUARTER("Order Date")
  • 2/22/1967 3:02:01 AM returns 1/1/1967 12:00:00 AM, the first day of the first fiscal quarter.

  • 6/17/1999 11:18:00 PM returns 4/1/1999 12:00:00 AM, the first day of the second fiscal quarter.

  • 9/2/2022 10:38:21 AM returns 7/1/2022 12:00:00 AM, the first day of the third fiscal quarter.

    Tip: Use QUARTER (expr) to calculate just the ordinal quarter from the returned timestamp.

Returns a timestamp for the first day in the quarter in which the input value occurs. For example, if the input timestamp occurs in the third fiscal quarter, the function returns the timestamp for July 1st.

EXTRACTQUARTER(expr)

Extract Second
EXTRACTSECOND("Order Date")
  • 1997/01/07 15:32:02.150 returns 1997/01/07 15:32:02.
  • 1997/01/07 20:44:18.163 returns 1997/01/07 20:44:18.

Returns a timestamp for the input value. For example, if the input timestamp is for 15:32:02.150, the function returns the timestamp for 15:32:02.

EXTRACTSECOND(expr)

Extract Week
EXTRACTWEEK("Order Date")

  • 2014/09/24 10:58:00 returns 2014/09/21.

  • 2014/08/13 11:10:00 returns 2014/08/10.

Returns the date of the first day of the week (Sunday) in which the input value occurs. For example, if the input timestamp is for Wednesday, September 24th, the function returns the timestamp for Sunday, September 21st.

EXTRACTWEEK(expr)

Extract Year
EXTRACTYEAR("Order Date")
  • 1967/02/22 03:02:01 returns 1967/01/01 00:00:00.
  • 1999/06/17 23:18:00 returns 1999/01/01 00:00:00.

Returns a timestamp for January 1st for the year in which the input value occurs. For example, if the input timestamp occurs in 1967, the function returns the timestamp for January 1st, 1967.

EXTRACTYEAR (expr)

Display Functions

Display functions operate on the result set of a query.

Function Example Description Syntax

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 n th 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're 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 Functions

Evaluate functions are database functions that can be used to pass through expressions to get advanced calculations.

Embedded database functions can require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Function Example Description Syntax

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])

Mathematical Functions

The mathematical functions described in this section perform mathematical operations.

Function Example Description Syntax

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)

Calculates the arc tangent of a numeric expression.

expr is any expression that evaluates to a numerical value.

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.

FLOOR(expr)

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)

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)

Running Aggregate Functions

Running aggregate functions perform operations on multiple values to create summary results.

Function Example Description Syntax

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

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

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

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

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

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)

Spatial Functions

Spatial functions enable you to perform geographical analysis when you model data. For example, you might calculate the distance between two geographical areas (known as shapes or polygons).

Note:

You can't use these spatial functions in custom calculations for visualization workbooks.
Function Example Description Syntax
GeometryArea

GeometryArea(Shape)

Calculates the area that a shape occupies.

GeometryArea(Shape)

GeometryDistance

GeometryDistance(TRIP_START, TRIP_END)

Calculates the distance between two shapes.

GeometryDistance(Shape 1, Shape 2)
GeometryLength

GeometryLength(Shape)

Calculates the circumference of a shape.

GeometryLength(Shape)

GeometryRelate

GeometryRelate(TRIP_START, TRIP_END)

Determines whether one shape is inside another shape. Returns TRUE or FALSE as a string (varchar).

GeometryRelate(Shape 1, Shape 2)

GeometryWithinDistance

GeometryWithinDistance(TRIP_START, TRIP_END, 500)

Determines whether two shapes are within a specified distance of each other. Returns TRUE or FALSE as a string (varchar).

GeometryWithinDistance(Shape1, Shape2, DistanceInFloat)

String Functions

String functions perform various character manipulations. They operate on character strings.

Function Example Description Syntax

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.

You must use raw data, not formatted data, with CONCAT.

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

LOCATEN('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.

integer is 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. For example, "d".

expr2 is any expression that evaluates to a character string. Identifies the target string to be searched. For example, "abcdef".

POSITION(expr1, 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.

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(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)

System Functions

The USER system function returns values relating to the session. For example, the user name you signed in with.

Function Example Description Syntax

DATABASE

  

Returns the name of the subject area to which you're logged on.

DATABASE()

USER

  

Returns the user name for the semantic model to which you're logged on.

USER()

Time Series Functions

Time series functions enable you to aggregate and forecast data based on time dimensions. For example, you might use the AGO function to calculate revenue from one year ago.

Time dimension members must be at or below the level of the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query.

You can also apply these functions to subject area data using the defined time-level hieararchies in the subject area. See Create Calculated Data Elements in a Workbook.

  • You can apply these functions in the My Calculations area in the workbook designer. See Create Calculated Data Elements in a Workbook.
  • You can also apply these functions to subject area data using the defined time-level hieararchies in the subject area.
Function Example Description Syntax

AGO

SELECT Year_ID, AGO(sales, year, 1)

Calculates the aggregated value of a measure in a specified time period in the past. For example, to calculate monthly revenue one year ago, use AGO(Revenue, Year, 1, SHIP_MONTH). To calculate quarterly revenues in the last quarter, use AGO(Revenue, Quarter, 1).

AGO(MEASURE, TIME_LEVEL, OFFSET)

Where:

  • MEASURE represents the measure to calculate, for example, revenue.
  • TIME_LEVEL represents the time interval, which must be Year, Quarter, Month, Week, or Day.
  • OFFSET represents the number of time intervals to calculate back to, for example, 1 for one year.

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.

PERIODROLLING(measure, x [,y])

Where:

  • measure represents the name of a measure column.
  • x is an integer that represents the offset from the current time.
  • y is an integer that represents the number of time units over which the function computes.
  • hierarchy is an optional argument that represents the name of a hierarchy in a time dimension such as yr, mon, day, that you want to use to compute the time window.

TODATE

SELECT Year_ID, Month_ID, TODATE (sales, year)

Calculates the aggregated value of a measure from the start of a time period to the latest time period, for example, year to date calculations.

For example, to calculate Year to Date Sales, use TODATE(sales, year).

TODATE(MEASURE, TIME_LEVEL)

Where:

  • MEASURE represents an expression that references at least one measure column, for example, sales.
  • TIME_LEVEL represents the time interval, which must be Year, Quarter, Month, Week, or Day.

FORECAST Function

Creates a time-series model of the specified measure over the series using Exponential Smoothing (ETS) or Seasonal ARIMA or ARIMA. This function outputs a forecast for a set of periods as specified by the numPeriods argument.

Syntax FORECAST(measure, ([series]), output_column_name, options,[runtime_binded_options])])

Where:

  • measure represents the measure to forecast, for example, revenue data.

  • series represents the time grain used to build the forecast model. The series is a list of one or more time dimension columns. If you omit series, then the time grain is determined from the query.

  • output_column_name represents the valid column names of forecast, low, high, and predictionInterval.

  • options represents a string list of name/value pairs separated by a semi-colon (;). The value can include %1 ... %N specified in runtime_binded_options.

  • runtime_binded_options represents a comma separated list of columns and options. Values for these columns and options are evaluated and resolved during individual query execution time.

FORECAST Function Options The following table list available options to use with the FORECAST function.

Option Name Values Description
numPeriods Integer The number of periods to forecast.
predictionInterval 0 to 100, where higher values specify higher confidence The confidence level for the prediction.
modelType

ETS (Exponential Smoothing)

SeasonalArima

ARIMA

 The model to use for forecasting.
useBoxCox

TRUE

FALSE

 If TRUE, then use Box-Cox transformation.
lambdaValue Not applicable

The Box-Cox transformation parameter.

Ignore if NULL or when useBoxCox is FALSE.

Otherwise the data is transformed before the model is estimated.
trendDamp

TRUE

FALSE

This is specific to the Exponential Smoothing model.

If TRUE, then use damped trend. If FALSE or NULL, then use non-damped trend.

errorType

Not applicable

 This is specific to the Exponential Smoothing model.
trendType

N (none)

A (additive)

M (multiplicative)

Z (automatically selected)

 This is specific to the Exponential Smoothing model
seasonType

N (none)

A (additive)

M (multiplicative)

Z (automatically selected)

 This is specific to the Exponential Smoothing model
modelParamIC

ic_auto

ic_aicc

ic_bic

ic_auto (this is the default)

 The information criterion (IC) used in the model selection.

Revenue Forecast by Day Example

This example selects revenue forecast by day.

FORECAST("A - Sample Sales"."Base Facts"."1- Revenue" Target,
("A - Sample Sales"."Time"."T00 Calendar Date"),'forecast', 'numPeriods=30;predictionInterval=70;') ForecastedRevenue

Revenue Forecast by Year and Quarter Example

This example selects revenue forecast by year and quarter.

FORECAST("A - Sample Sales"."Base Facts"."1- Revenue",
("A - Sample Sales"."Time"."T01 Year" timeYear, "A - Sample Sales"."Time"."T02 Quarter" TimeQuarter),'forecast', 'numPeriods=30;predictionInterval=70;') ForecastedRevenue