Appendix: Insights Functions
This article contains: |
1. List of built in Functions |
Function | Description | Result Data Types | Examples |
---|---|---|---|
AVG Numeric, Money | Returns the average of the values in a group. Null values are ignored. | Numeric, Money | AVG([Retail].[dbo].[Orders].[Freight]) |
COUNT Any data type except Image and Lob. | Returns the number of items in a group. | Numeric. | COUNT([Retail].[dbo].[Orders].[OrderID]) |
MAX 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 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 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 Text. | Returns the number of characters of the given text expression, excluding trailing blanks. | Numeric. | LEN([Retail].[dbo].[Orders].[ShipAddress]) |
ROUND 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 Text. | Returns the concatenation of all the parameters in that exact order. | Text. | CONCAT('ab','cd',[SHIPCOUNTRY]) |
GETDATE N/A. | Returns the current system date and time. | Datetime. | GETDATE() |
DATEADD (See table List of Dateparts and Abbreviations below) | Returns a new datetime value based on adding an interval to the specified date. | Datetime. | DATEADD(day,3,[DueDate]) |
DATEDIFF | Returns the number of date and time boundaries crossed between two specified dates. | Numeric. | DATEDIFF(day,[OrderDate],[ShipDate]) |
DATEPART | Returns a number representing the specified datepart of the specified date. | Numeric. | DATEPART(DAY,[Retail].[dbo].[Orders].[OrderDate]) |
CONVERT | 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 | 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 | 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 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. | Returns TRUE when both expressions are TRUE, otherwise returns FALSE. | Boolean. | CASE WHEN ([Retail].[dbo].[Orders].[EmployeeID] = 1 AND [Retail].[dbo].[Orders].[CustomerID]='DELDG') THEN1000 else [Retail].[dbo].[Orders].[EmployeeID] end |
OR Boolean. | Returns TRUE when either expression is TRUE, otherwise returns FALSE. | Boolean. | CASE WHEN ([Retail].[dbo].[Orders].[EmployeeID]=1OR[Retail].[dbo].[Orders].[EmployeeID]=2)THEN1000 else [Retail].[dbo].[Orders].[EmployeeID] end |
DISTINCT Any data type except Image and Lob. | Returns unique values. | The same data type as expression. | COUNT(DISTINCT([Northwind].[dbo].[Orders].[ShipCity])) |
IFF | 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 | 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 THEN (result_expression) […n] 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 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 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_expressions 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 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 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 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]) |
2. 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 |
<< Previous section
Expressions for Calculated Fields