Calculated Fields Function List for Advanced Analytics

Returns TRUE when both expressions are TRUE, otherwise returns FALSE.

This is most commonly used as part of an IF or CASE statement.

expression_one = 'x' AND expression_two = 'y'

CASE
WHEN
([DataWarehouse].[dm].[MobileForm].[QuestionText]= 'Were you injured?'
AND
[DataWarehouse].[dm].[MobileFormResponse].[ResponseValueText]= 'Yes')
THEN 'Injury Reported'
ELSE 'Safe Day'
END

Returns TRUE if the test value is equal to or between the two expression values.

This is most commonly used as part of an IF or CASE statement.

BETWEEN(test_value, start_expression,end_expression)

IF(
BETWEEN([DataWarehouse].[dm].[User].[DateOfBirth], '01/01/2001','12/31/2001')
THEN 'Snake'
ELSE 'other'
END

Converts an expression from one data type to another.

You can use either this or the CONVERT function.
See the table below for a list of possible data types.

CAST(expression AS new_data_type)

CAST('06/16/1966' AS DATETIME)
CAST('10.5' AS MONEY)
CAST('10' AS NUMERIC)

CONCAT(string_1, string_2)

CONCAT([DataWarehouse].[dm].[Job].[JobTitle], ' - ',[DataWarehouse].[dm].[Job].[RequisitionCode])

Converts an expression from one data type to another.

You can use either this or the CAST…AS function.
See the table below for a list of possible data types.

CONVERT(new_data_type, expression)

CONVERT(DATETIME, '06/16/1966')
CONVERT(MONEY, '10.5')
CONVERT(NUMERIC, '10')

COUNT(field)

COUNT([DataWarehouse].[dm].[JobCandidate].[JobCandidateId])

Returns the date field plus the provided increment number.

datepart: the part of the date; ex day, month, year). See the Date Part table below
increment_number: the value added to date_field
date_field: input field

DATEADD(datepart,increment_number, date_field)

DATEADD(yyyy, 2,[DataWarehouse].[dm].[User].[HireDate])
This adds 2 years to the HireDate

DATEADD(mi, -17,[DataWarehouse].[dm].[TimeRecord].[StopDatetime])
This subtracts 17 minutes from the StopDatetime

Returns the number of date units (like minutes, hours, ormonths) between two dates.

datepart: the part of the date; ex day, month, year). See the Date Part table below

start_datetime, end_datetime: two date fields

DATEDIFF(datepart, start_datetime, end_datetime)

DATEDIFF(dd,[DataWarehouse].[dm].[UserLearningPlan].[AssignedDate],GETDATE())

Returns part of a supplied date field.

datepart: the part of the date; ex day, month, year). See the Date Part table below

date_field: date field from one of the tables in the report

Returns unique values that are listed in a field.

This would typically be used within another function, like SUM or COUNT.

Returns the value of the true_expression of theboolean_expression is TRUE, otherwise returns the value of thefalse_expression.

If you have multiple values that you want to test for you can embed an IF statement with another IF statement as many times as you have conditions as you need to check for. Using the CASE statement is probably going to be easier to build and troubleshoot than this option.

Checks to see if a field is NULL. If it is NULL then it returns the value of the replacement expression. If it is not NULL then it returns the value of the original field.

Note, that in Analytics many of the fields, except for dates, that would normally be NULL have been replaced with blanks. In those cases, you should use an IIF, CASE, or IF statement to see if a field is blank.