Analytics Center Functions

List of Built-in Functions

Function	Description	Result Data Types	Examples
AVG AVG(expression) Numeric, Money	Returns the average of the values in a group. Null values are ignored.	Numeric, Money	AVG([Retail].[dbo].[Orders].[Freight])
COUNT COUNT(expression) Any data type except Image and Lob.	Returns the number of items in a group.	Numeric.	COUNT([Retail].[dbo].[Orders].[OrderID])
MAX MAX(expression) Any data type except Image and Lob.	Returns the maximum value in a group.	The same data type as expression.	MAX([Retail].[dbo].[Orders].[Freight])
MIN MIN(expression) Any data type except Image and Lob.	Returns the minimum value in a group.	The same data type as expression.	MIN([Retail].[dbo].[Orders].[Freight])
SUM SUM(expression) Numeric, Money.	Returns the sum of all the values in a group. Null values are ignored.	The same data type as expression.	SUM([Retail].[dbo].[Orders].[Freight])
LEN LEN(expression) Text.	Returns the number of characters of the given text expression, excluding trailing blanks.	Numeric.	LEN([Retail].[dbo].[Orders].[ShipAddress])
ROUND ROUND(expression) Numeric, Money.	Returns the expression rounded to the specified length or precision.	The same data type as expression.	ROUND([Retail].[dbo].[Orders].[Freight],0)
CONCAT CONCAT(expression, expression [, ...]) Text.	Returns the concatenation of all the parameters in that exact order.	Text.	CONCAT('ab','cd',[SHIPCOUNTRY])
GETDATE GETDATE() N/A.	Returns the current system date and time.	Datetime.	GETDATE()
DATEADD DATEADD(datepart, number, date) datepart: the part of the date. (See table List of Dateparts and Abbreviations) number: the value used to increment datepart. date: an expression that returns a datetime value.	Returns a new datetime value based on adding an interval to the specified date.	Datetime.	DATEADD(day,3,[DueDate])
DATEDIFF DATEDIFF(datepart, startdate, enddate) datepart: the part of the date. (See table List of Dateparts and Abbreviations) startdate, enddate: expressions that return datetime values.	Returns the number of date and time boundaries crossed between two specified dates.	Numeric.	DATEDIFF(day,[OrderDate],[ShipDate])
DATEPART DATEPART(datepart, date) datepart: the part of the date. (See table List of Dateparts and Abbreviations) date: an expression that returns a datetime value.	Returns a number representing the specified datepart of the specified date.	Numeric.	DATEPART(DAY,[Retail].[dbo].[Orders].[OrderDate])
CONVERT CONVERT(data_type, expression) data_type: any data type. expression: any expression.	Explicitly converts an expression of one data type to another, similar to CAST..AS.	The same data type as data_type.	CONVERT(TEXT,[Retail].[dbo].[Orders].[OrderDate])
CAST..AS CAST(expression AS data_type) data_type: any data type. expression: any expression.	Explicitly converts an expression of one data type to another, similar to CONVERT.	The same data type as data_type.	CAST([Retail].[dbo].[Orders].[OrderID] AS TEXT)
ISNULL ISNULL(check_expression, replacement_expression) check_expression and replacement_expression: any data type.	Returns the value of check_expression if it is not NULL; otherwise, returns the value of replacement_expression.	The same data type as expression.	ISNULL([Retail].[dbo].[Orders].[ShipRegion] , 'No Region')
BETWEEN..AND BETWEEN(expression, begin_expression, end_expression) Any data type except Image and Lob.	Returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression, otherwise returns FALSE.	Boolean.	CASE WHEN (BETWEEN ([Retail].[dbo].[Orders].[EmployeeID],1 , 3)) THEN 1000 else [Retail].[dbo].[Orders].[EmployeeID] END
AND boolean_expression AND boolean_expression Boolean.	Returns TRUE when both expressions are TRUE, otherwise returns FALSE.	Boolean.	CASE WHEN ([Retail].[dbo].[Orders].[EmployeeID] = 1 AND [Retail].[dbo].[Orders].[CustomerID] = 'DELDG') THEN 1000 else [Retail].[dbo].[Orders].[EmployeeID] end
OR boolean_expression AND boolean_expression Boolean.	Returns TRUE when either expression is TRUE, otherwise returns FALSE.	Boolean.	CASE WHEN ([Retail].[dbo].[Orders].[EmployeeID] = 1 OR [Retail].[dbo].[Orders].[EmployeeID] = 2) THEN 1000 else [Retail].[dbo].[Orders].[EmployeeID] end
DISTINCT DISTINCT (expression) or DISTINCT expression Any data type except Image and Lob.	Returns unique values.	The same data type as expression.	COUNT(DISTINCT([Northwind].[dbo].[Orders].[ShipCity]))
IFF IFF (boolean_expression, true_expression[, false_expression]) boolean_expression: Boolean. true_expression, false_expression: any data type except Image and Lob.	Returns the value of true_expression when boolean_expression is TRUE, otherwise returns the value of false_expression.	The highest precedence data type from data types of true_expression and false_expression.	IIF([Retail].[dbo].[Orders].[EmployeeID] = 2, 200, [Retail].[dbo].[Orders].[EmployeeID])
IF..THEN..ELSE..END IF (boolean_expression) THEN (true_expression) [ELSE (false_expression)] END boolean_expression: Boolean. true_expression, false_expression: any data type except Image and Lob.	Returns the value of true_expression when boolean_expression is TRUE, otherwise returns the value of false_expression.	The highest precedence data type from data types of true_expression and false_expression.	IF ([northwind].[dbo].[Orders].[EmployeeID] < 3) then 'Less' else ( IF (BETWEEN ([northwind].[dbo].[Orders].[EmployeeID] , 3, 6)) then  'More' else 'Most' END) END
CASE WHEN..THEN..ELSE..END CASE WHEN (when_expression) THEN (result_expression) […n] [ELSE (else_result_expression)] END Any data type except Image and Lob.	Returns the value of result_expression matching the first when_expression with the value equal to input_expression, otherwise return the value of else_result_expression.	The highest precedence data type from data types of all result_expression s and else_result_expression.	Case when ([northwind].[dbo].[Orders].[EmployeeID] = 1) then 'less' when ([northwind].[dbo].[Orders].[EmployeeID] = 3 ) then 'mid' when ([northwind].[dbo].[Orders].[EmployeeID] = 4)  then 'high' else 'not evaluated' end
CASE..WHEN..THEN..ELSE..END CASE (input_expression) WHEN (when_expression) THEN (result_expression) […n] [ELSE (else_result_expression)] END Any data type except Image and Lob.	Returns the value of result_expression matching the first when_expression with the value equal to input_expression, otherwise return the value of else_result_expression.	The highest precedence data type from data types of all result_expression s and else_result_expression.	CASE 'USA' WHEN  [Retail].[dbo].[Orders].[ShipCountry] THEN 1 else [Retail].[dbo].[Orders].[OrderID] END
**CASE WHEN…THEN…ELSE…END CASE WHEN (when_expression) THEN (result_expression) […n] [ELSE (else_result_expression)] END Any data type except Image and Lob.	Returns the value of result_expression matching the first when_expression with the value equal to input_expression, otherwise return the value of else_result_expression.	The highest precedence data type from data types of all result_expression s and else_result_expression.	Case when ([northwind].[dbo].[Orders].[EmployeeID] = 1) then 'less' when ([northwind].[dbo].[Orders].[EmployeeID] = 3 ) then 'mid' when ([northwind].[dbo].[Orders].[EmployeeID] = 4)  then 'high' else 'not evaluated' end
RUNNINGSUM RUNNINGSUM(expression) Numeric, Money.	Returns the sum of all the values of expression from the first row up to the current row.	The same data type as expression.	RUNNINGSUM([Retail].[dbo].[Orders].[Freight])
RUNNINGAVG RUNNINGAVG(expression) Numeric, Money.	Returns the average of all the values of expression from the first row up to the current row.	The same data type as expression.	RUNNINGAVG([Retail].[dbo].[Orders].[Freight])
RUNNINGCOUNT RUNNINGCOUNT(expression) Any data type except Image and Lob.	Returns the number of unique values of expression from the first row up to the current row.	Numeric.	RUNNINGCOUNT([Retail].[dbo].[Orders].[OrderID])

List of Dateparts and Abbreviations

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	ww, wk
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms