Marketing Intelligence -- Functions

This topic lists the Functions that can be used to create calculated fields and measures in Ad Hoc Views.

Absolute Value

This function returns the absolute value of a number or field, that is, the non-negative value of the number. The syntax for this function is:

Absolute (NumericExpression)

Examples:

• Show the magnitude of each transaction, regardless of whether the transaction is positive or negative:

Absolute("Transaction Amount")

• Compute a positive commission on all transactions, regardless of whether the transaction is positive or negative:

"Commission Rate" * Absolute("Transaction Amount")

Average

This function returns the average value of a measure or numeric field, based on an optional level. Null values are not included. The syntax for this function is:

Average ("NumberFieldName", 'Level')

Level can be one of the following: "Current (default)," "ColumnGroup," "ColumnTotal," "RowGroup," "RowTotal," or "Total."

Example:

Average("Salary", 'RowGroup')

 

Concatenate

This function combines multiple text strings and/or fields into a single text field. Text strings are enclosed in single quotes; labels for fields or measures are enclosed in straight quotes. The syntax for this function is:

Concatenate("TextFieldName" , 'text string').

Examples:

Concatenate("Last Name", ' , ', "First Name")

Concatenate("Product Category", ' -- ', "Product Name")

Contains

This Boolean functions returns "true" if the first argument contains the second argument; otherwise, it returns "false." Text strings are enclosed in single quotes; labels for fields or measures are enclosed in straight quotes. The syntax for this function is:

Contains("TextFieldName", 'text string')

Example:

Contains("Product Name", 'Soda')

Count All

This function returns the count of non-null items in a field or measure; this function always returns a non-negative integer. The syntax for this function is:

CountAll("FieldName", 'Level')

Level can be one of the following: "Current (default)," "ColumnGroup," "ColumnTotal," "RowGroup," "RowTotal," or "Total."

Example:

• Count the total number of non-null transactions in the specified group:

CountAll("TransactionAmount", 'RowGroup')

Count Distinct

This function returns the distinct count of non-null items in the input; this function always returns a non-negative integer. The syntax for this function is:

CountDistinct("FieldName", 'Level')]

Level can be one of the following: "Current (default)," "ColumnGroup," "ColumnTotal," "RowGroup," "RowTotal," or "Total."

Example:

• Count the number of distinct customers:

CountDistinct("CustomerName", 'Total')

Day - Name

This function, given a date field, returns a text field with the name of the day of the week. The syntax for this function is:

DayName("DateFieldName")

Examples:

• Display the day of the week on which a store was opened:

DayName("OpenDate")

• Combine with the Mode function to display the day of the week on which the most stores were opened:

Mode(DayName("OpenDate"), 'Total')

Day - Number

This function, given a date field, returns a numeric field with the day of the month. The syntax for this function is:

DayNumber("DateFieldName")

Example:

• Display the day of the month on which a store was opened:

DayNumber("OpenDate")

Ends With

This Boolean functions returns "true" if the first argument ends with the string specified in the second argument; otherwise, it returns "false." Text strings are enclosed in single quotes; labels for fields or measures are enclosed in straight quotes. The syntax for this function is:

EndsWith("TextFieldName", 'text string')

Example:

EndsWith("Product Name", 's')

If

This function, given a Boolean field or calculation as the first argument, returns the second argument if true; optionally, it returns the third argument if false. It returns "null" if the first argument is null. The syntax for this function is:

IF ("BooleanFieldName", ExpressionWhenTrue, ExpressionWhenFalse)

ExpressionWhenFalse must be of the same type as ExpressionWhenTrue. For example, if ExpressionWhenTrue is a date, ExpressionWhenFalse must also be a date in the same format. If ExpressionWhenFalse is not set, then a false result returns a null value.

You can create a Boolean Expression using the comparison operators ("==," "!=," ">," ">=," "<," "<="), any functions that return Boolean values (StartsWith, EndsWith, IsNull, Contains), and logical operators ("and," "or," "not").

When dates are used in comparisons or the IF function, they must be the same type (date only, date/time, or time only). Make sure to use the correct modifier (d, ts, t) when using date constants in comparisons.

Example:

• Use the Contains function to see whether the product name contains the string "Soda." If it does, set the field value to "Yes;" if not, set the field value to "No."

IF(Contains("Product Name", 'Soda'), 'Yes', 'No')—

Is Null

This Boolean function returns "true" if the field value is null; otherwise, it returns "false." The syntax for this function is:

IsNull("FieldName")

Example:

IsNull("FirstName")

Length

This function, given a text string, returns the string length. Null values will return null. The syntax for this function is:

Length("TextFieldName")

Example:

Length("FirstName")

Max

This function, given a numeric or date field, returns the maximum value reached by the specified field or calculation based on an optional level. The syntax for this function is:

Max("NumericOrDateFieldName", 'Level')

Level can be one of the following: "Current (default)," "ColumnGroup," "ColumnTotal," "RowGroup," "RowTotal," or "Total."

Example:

Max("Salary", 'RowGroup')

Median

For an odd number of values for a numeric or date field, this function returns the middle value after all values are listed in order. For an even number of values, the function returns the average of the middle two values. The syntax for this function is:

Median("NumericOrDateFieldName", 'Level')

Level can be one of the following: "Current (default)," "ColumnGroup," "ColumnTotal," "RowGroup," "RowTotal," or "Total."

Example:

Median("Salary", 'RowGroup')

Min

This function, given a numeric or date field, returns the minimum value reached by the specified field or calculation based on an optional level. The syntax for this function is:

Min("NumericOrDateFieldName", 'Level')

Level can be one of the following: "Current (default)," "ColumnGroup," "ColumnTotal," "RowGroup," "RowTotal," or "Total."

Example:

Min("Salary", 'RowGroup')

Month - Name

This function, given a date field, returns a text field with the name of the month. The syntax for this function is:

MonthName("DateFieldName")

Example:

MonthName("OrderDate")

Month - Number

This function, given a date field, returns the number of the month, such as January = 1, or December = 12, for example. Null values will return null. The syntax for this function is:

MonthNumber("DateFieldName")

Example:

MonthNumber("OrderDate")

Sum

This function returns the sum of all values in the range based on an optional level. Null values are excluded. The syntax for this function is:

Sum("NumericFieldName", 'Level')

Level can be one of the following: "Current (default)," "ColumnGroup," "ColumnTotal," "RowGroup," "RowTotal," or "Total."

Example:

Sum("Sales", 'RowGroup')

Today

This function calculates the date that is the specified number of days from the current system date. The syntax for this function is:

Today(Integer)

Example:

• The current system date:

Today(0)

• The day after the current system date:

Today(1) —

• The day before the current system date:

Today(-1)

Year

This function, given a date field, returns the year. The syntax for this function is:

Year("DateFieldName")

Example:

Year("OrderDate")

 

 Copyright © Marigold    Marigold Engage+ Online Help v24.8.0  August 2024