Functions

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

Topics:

Aggregate Functions

Aggregate functions perform operations on multiple values to create summary results.

The following list describes the aggregation rules that are available for columns and measure columns. The list also includes functions that you can use when creating calculated items for analyses.

Default — Applies the default aggregation rule as in the semantic model or by the original author of the analysis. Not available for calculated items in analyses.
Server Determined — Applies the aggregation rule that's determined by the Oracle BI Server (such as the rule that is defined in the semantic model). The aggregation is performed within Oracle BI Server for simple rules such as Sum, Min, and Max. Not available for measure columns in the Layout pane or for calculated items in analyses.
Sum — Calculates the sum obtained by adding up all values in the result set. Use this for items that have numeric values.
Min — Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this for items that have numeric values.
Max — Calculates the maximum value (highest numeric value) of the rows in the result set. Use this for items that have numeric values.
Average — Calculates the average (mean) value of an item in the result set. Use this for items that have numeric values. Averages on tables and pivot tables are rounded to the nearest whole number.
First — In the result set, selects the first occurrence of the item for measures. For calculated items, selects the first member according to the display in the Selected list. Not available in the Edit Column Formula dialog box.
Last — In the result set, selects the last occurrence of the item. For calculated items, selects the last member according to the display in the Selected list. Not available in the Edit Column Formula dialog box.
Count — Calculates the number of rows in the result set that have a non-null value for the item. The item is typically a column name, in which case the number of rows with non-null values for that column are returned.
Count Distinct — Adds distinct processing to the Count function, which means that each distinct occurrence of the item is counted only once.
None — Applies no aggregation. Not available for calculated items in analyses.
Server Complex Aggregate — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the semantic model). The aggregation is performed by the Oracle BI Server, rather than within Presentation Services. Not available for calculated items in analyses.
Report-Based Total (when applicable) — If not selected, specifies that the Oracle BI Server should calculate the total based on the entire result set, before applying any filters to the measures. Not available in the Edit Column Formula dialog box or for calculated items in analyses. Only available for attribute columns.

Function	Example	Description	Syntax
AGGREGATE AT	`AGGREGATE(sales AT year)`	Aggregates columns based on the level or levels in the data model hierarchy 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 can't 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 can't write the function as `AGGREGATE(yearly_sales AT month)` if month is from the same time dimension used as the measure level for yearly_sales.	`AGGREGATE(measure AT level [, level1, levelN])`
AGGREGATE BY	`AGGREGATE(sales BY month, region)`	Aggregates a measure based on one or more dimension columns. measure is the name of a measure column to aggregate. `column` is the dimension column at which you want to aggregate. You can aggregate measures based more than one column.	`AGGREGATE(measure BY column [, column1, columnN])`
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)`	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 is 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'reare 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)`

Tips on Using Aggregate Functions

Here're some tips on getting the best results when using aggregate functions in your workbooks.

Tips:

First, Last. Avoid using the First and Last aggregations with a 100% stacked bar chart type. Adding current date to By adds grain to the query, which fetches all rows for the visualization plus the current date. The Last function then returns the last row in that result set, which varies depending on how the data is returned from the source.
Min - Use Min to calculate the smallest value in a set of rows. To find the smallest value in a set of columns, use Evaluate. For example:
evaluate('least(%1,%2,%3)',column date 1,date 2,date 3)

Analytics Functions

Analytics functions allow you to explore data using models such as forecast, trendline, and cluster. Alternatively, you can drag and drop analytics functions into the workbook editor.

Alternatively, you can add forecasts, trendlines, and clusters to a workbook by selecting them on the Analytics tab of the Data Panel in the workbook editor. See Add Statistical Analytics Functions to Visualizations.

Function	Example	Description	Syntax
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])`
FORECAST	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	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. See also additional FORECAST Function Options below.	`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. See also additional FORECAST Function Options below.
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])`
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)`

FORECAST Function Options The following table lists 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.

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 and Time Functions

Date and time functions manipulate data based on DATE and DATETIME.

Function	Example	Description	Syntax
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)`

Tips on Using Date Functions

Here're some tips on getting the best results when using date functions in your workbooks to calculate fiscal periods, convert strings to dates, show a date and time in a specific time zone, and calculate differences over time.

Calculate fiscal year, quarter and month:

Subject areas typically have a time dimension, so you can use the pre-configured periods such as fiscal quarter, fiscal month, and fiscal week. If you only have a date field, you can calculate these periods yourself using date functions.

This example calculates fiscal quarter:

'FY'||cast(YEAR(TIMESTAMPADD(SQL_TSI_MONTH, 7,<date field>)) as char)||'Q'||cast(QUARTER(TIMESTAMPADD(SQL_TSI_MONTH, 7,<same date field>)) as char)

This example calculates fiscal year and month:

'FY'||cast(YEAR(TIMESTAMPADD(SQL_TSI_MONTH, 7,<date field>)) as char)||cast(MONTHNAME(<same date field>) as char)

Notes:

|| concatenates values.
cast (xxx(date) as char) extracts a portion of a date field.
TIMESTAMPADD adds (or substracts) periods from a date. This example uses the parameter value SQL_TSI_MONTH to add months.

The calculations above work well in tables but when used in filters the spacing between returned values might look odd. In this case, use a cleaner but more complex calculation based on these examples:

'FY'||evaluate ('to_char(%1,%2)'as char,(EVALUATE('add_months(%1,%2)' as date,<date field>,7)),'YY')||'-Q'||evaluate ('to_char(%1,%2)'as char,(EVALUATE('add_months(%1,%2)' as date,<same date field>,7)),'Q')

'FY'||evaluate ('to_char(%1,%2)'as char,(EVALUATE('add_months(%1,%2)' as date,<date field>,7)),'YY')||'-'||evaluate ('to_char(%1,%2)'as char,<same date field>,'MON')

Convert a string to a date:

Oracle Analytics profiles your data and provides enrichment recommendations for extracting and converting dates. If you need to convert a string to a date yourself, use these tips.

In the workbook designer, navigate in the data pane to the column that you wish to convert, then right-click and select Convert to Date. Configure the date then click Add Step.

Description of the illustration convert-string-date.png
In the workbook designer, navigate in the data pane to the column that you wish to convert, then right-click and select Edit. Configure the date then click Add Step.

Description of the illustration convert-string-date-2.png

In the text box containing the name of your column in blue, insert To_DateTime( before the column name, then add the date format you want to use after the column name, followed by ), then click Add Step.

Description of the illustration convert-string-date-3.png

When you use the Edit option to create your date-conversion function, you can also use Allow_Variable_Digits to process column values with one-digit or two digit numbers, and Null_On_Error arguments to discard rows that don't match the pattern required. See also REPLACE and CAST for alternative ways to process data that doesn't match the pattern required.

As an alterntive to using To_DateTime(), use CAST() to change a value's data type. For example, CAST(SalesDate AS DATE).

Additionally, use the Replace option. In the workbook designer, navigate in the data pane to the column that you wish to convert, then right-click and select Replace. Configure the replacement type you want, then click Add Step.

Show a date and time field in a specific timezone:

Use the NEW_TIME function in the database in conjunction with EVALUATE. For example:

evaluate ('new_time(%1,%2,%3)' as char, "DV - Pipeline and Forecast"."Opportunity"."Last Updated Date",'GMT','AST')

Make sure that the date field includes the time component.

Calculate difference over time:

To show the difference in number and percentage between now and yesterday create calculations based on these examples:

Calculate a measure until yesterday: filter (Measure using date <= TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))
Calculate the difference: Measure - filter (Measure using date <= TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))
Calculate the difference percentage: 1-(filter (Measure using date <= TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))/Measure display as percentage

Notes:

These examples use SQL_TSI_DAY, however you can alternatively use WEEK and MONTH week over week and month over month.
These examples work with a normal or natural calendar. If you need fiscal months and quarters, use repository variables in your calculations instead of TIMESTAMPADD. Alternatively, combine this with the Calculate fiscal year, quarter and month tip.
Some subject areas include pre-calculated Prior X measures like PY (prior year), PQ (prior quarter) that you can use to calculate differences over time.
Alternatively, you can use the TIME SERIES functions: AGO, PERIODROLLING and TODATE. See Time Series Functions.

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. Note: If the first day of a week (i.e. Sunday) falls in a previous year and would therefore adversely affect the aggregation, the function returns the 7th day of the week (i.e. Saturday) in the current year instead of the first day of the week in the previous year. For example, 1/1/24, 1/2/24, and 1/3/24 all aggregate to Saturday 1/6/24, rather than Sunday 12/29/23.	`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)`

Tips on Using Date Dimensions in Subject Areas

Here're some tips on getting the best results when using subject area dates in calculations.

Adding a Subject Area Date to a Calculation

If you drag a date from a subject area directly into a calculation and process it as a string or an integer, you'll get an error. This happens because the underlying date value is a timestamp.

Instead, use one of the Date Extraction Functions to interpret the date.

For example, you might have these subject area dates.
Description of tips-using-dates-1.png follows
Description of the illustration tips-using-dates-1.png

To extract months from these subject area dates, use the ExtractMonthOfYear function:

case when monthname(ExtractMonthOfYear("Date")) in ('Jan' ,'Feb', 'Mar') 
THEN 'Q1' 
ELSE 'Rest of the year' END

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

Tips on Using Display Functions

FILTER - If you're building a report using a subject area, use hierarchies defined in the subject area instead of filtering hierarchy columns directly in a calculation. In other words, if a subject area has a hierarchy for Time\Fiscal Year\Fiscal Quarter, then avoid:
filter (<measure> using fiscal_quarter = 'Q4')

filter (<measure> using fiscal_quarter = 'Q3')

filter (<measure> using fiscal_year = 'FY24')

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

Tips on Using String Functions

Here're some tips on getting the best results when using string functions in your workbooks.

Concatenate multiple values:

Use LISTAGG to concatentate multiple values into one cell and one row.

listagg(<column to concatenate> by <grouping column>)

Add on overflow truncate if the resulting string is too long, and add distinct if the values aren't unique.

For example:

listagg(distinct City_ID by STATE on overflow truncate)

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.

Function Example Description Syntax

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)`	Calculates 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 calculates. 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.

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)

Calculates 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 calculates.
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.