Sugar Discover Function Reference

Overview

This page serves as a reference guide for customers to add calculated fields to their datasets or reports.

Prerequisites

To add calculated fields, you must have access to Discover and have a Discover role of Admin or User. 

Arithmetic Operators

Arithmetic operators are used to perform common arithmetic operations between field values in your calculated field definitions. Supported arithmetic operators include:

Add ( + )

Adds the value of one field to the value of another field (or constant).

Example: Calculate the total deal size of an opportunity by adding the values for ARR Amount and Services Amount:

  [ARR Amount] + [Services Amount]

Subtract ( - )

Subtracts the value of one field from another field value (or constant).

Example: Calculate the amount of recurring revenue for an opportunity by subtracting Services Amount from Amount:

  [Amount] - [Services Amount]

Multiply ( * )

Multiplies the value of one field by the value of another field (or constant).

Example: Calculate the ARR of an opportunity by multiplying the MRR Amount times a constant 12:

  [MRR Amount] * 12

Divide ( / )

Divides the value of one field by the value of another field (or constant).

Example: Calculate the MRR of an opportunity by dividing the ARR Amount by a constant of 12:

  [ARR Amount] / 12

Boolean Operators

Boolean operators are used to combine conditions in a Calculated Field definition. For example, boolean operators are often used to define conditions with a CASE statement. Supported boolean operators include:

• AND
• OR
• NOT
• < (less than)
• <= (less than or equal to)
• > (greater than)
• >= (greater than or equal to)
• = (equals)

For an example illustrating the use of boolean operators, please see the Case example.

Average

The AVERAGE function returns the average (arithmetic mean) of the input expression values. The AVERAGE function works with numeric values and ignores NULL values.

Syntax:

  AVERAGE ( {number field} )

Arguments:

• number field: A reference to a number type field in your dataset.

Example: Return the average number of days from the field Days in Stage:

  AVERAGE ( [Days in Stage] )

Case

The CASE statement is a conditional expression, similar to if/then/else statements found in other languages or the SWITCH function in Excel. CASE is used to specify a result based on one or more conditions.

Each condition in the CASE statement is evaluated based on a Boolean expression, and the CASE statement returns the specified result value for the first condition that evaluates to true. If no true conditions are found, the default value is returned.

Syntax:

  CASE ( 
boolean condition 1, result 1,
boolean condition 2, result 2,
...
boolean condition n, result n,
default value 
)

Arguments:

• boolean condition: A boolean condition is an expression that evaluates to true or false using Boolean Operators or Boolean Functions (i.e., IS_NULL, IS_NOT_NULL).
• result: Determines the value to return when the corresponding boolean condition evaluates to true.
  Note: All instances of your result arguments need to be of the same data type in order for the CASE statement to function properly.
• default value: Default value is an optional argument that determines the value to return when none of the specified boolean conditions evaluate to true. If the default value is not specified and none of the boolean conditions evaluate to true, the CASE statement will return a NULL value.

Example: Categorize values with labels representing 'Deal Size' based on the Amount field:

  CASE (
[Amount] > 0 AND [Amount] < 25000, "Small",
[Amount] >= 25000 AND [Amount] < 100000, "Medium",
[Amount] >= 100000, "Large",
"Not Specified"
)

Contains

The CONTAINS function searches the values of a string field for the criteria you define in the search term(s), and returns 'true' or 'false' based on whether the value was found or not. This function is commonly used as condition expressions within a Case statement for creating custom bucketing fields (i.e., Industry Category).

Syntax:

  CONTAINS( {string field}, search term 1, ..., search term N, case sensitive? )

Arguments:

• {string field}: A reference to the string type field in your dataset in which you want to search for the text specified in your search_argument.
• search term: Defines one or more text strings that you want to search for in the specified string field. You can use the ? and * wildcard characters to specify any single character (?) or any consecutive string of characters (*). Use ~? and ~* to search explicitly for the ? and * characters.
• case sensitive?: An optional flag specifying whether or not to perform a case-sensitive search for the text specified in your search_argument. If not specified, the function will perform a case insensitive search.

Example 1: Create a calculated field to bucket opportunities when Opportunity Name contains 'renewal' as 'Renewal', otherwise as 'New Business':

  CASE ( 
CONTAINS([Opportunity Name], "renewal"), "Renewal", "New Business"
)

Example 2: Determine if an Opportunity Name contains a date in 2018 with the format MM-DD-YYYY:

  CONTAINS ([Opportunity Name], "??-??-2018")

Count

The COUNT function computes the number of rows with non-NULL values for the specified field.

Syntax:

  COUNT ( {field} )

Arguments:

• field: A reference to a field in your dataset of any data type.

Example: Return the count of activities represented by Activity ID:

  COUNT ( [Activity ID] )

Count Distinct

The COUNT_DISTINCT function computes the number of distinct, non-NULL values for the specified field.

Syntax:

  COUNT_DISTINCT ( {field} )

Arguments:

• field: A reference to a field in your dataset of any data type.

Example: Return the number of distinct opportunities based on the Opportunity ID field:

  COUNT_DISTINCT ( [Opportunity ID] )

Date

DATE returns a string data type for specific month, day, or year from a date value.

Syntax:

  DATE ( {timestamp field|expression}, parameter )

Arguments:

• {timestamp field | expression}: A reference to a timestamp type field in your dataset or an expression that results in a timestamp like the function TODAY() which returns a timestamp representing the current date.
• parameter
  • d - returns the number representing the day of the week (Sun-Sat) starting with Sunday being 1.
  • dd - returns the two digit string representing the day of the month, i.e., "07".
  • DAY or Day or day - returns the UPPER/Proper/lower case text name for the day of the week, i.e., "MONDAY" or "Monday" or "monday".
  • mm - returns a two digit string representing the month number, i.e., "08" for August.
  • MONTH or Month or month - returns the UPPER/Proper/lower case text name for the month, i.e., "AUGUST" or "August" or "august".
  • MON or Mon or mon - returns the UPPER/Proper/lower case three letter abbreviation for month, i.e., "AUG" or "Aug" or "aug".
  • yyyy - returns an integer representing the year, i.e., "2020".

Example 1: If date value for Created Date is 2/12/2020, the below example returns 12:

  DATE ([Created Date], "dd" )

Example 2: If date value for Snapshot Date is 2/12/2020, the below Case example using a Date function returns 0:

  Case (DATE ( [Snapshot Date], "dd" ) = "15", 1,0)

Date Add

DATE_ADD increments a timestamp value by a specified interval.

Syntax:

  DATE_ADD ( datepart, interval, {timestamp field|expression} )

Arguments:

• datepart:  The specific part of the date value that you want to truncate to the beginning of. Supported values are: YEAR, QTR, MONTH, WEEK, DAY, HOUR, DATE or EXACT_DATE
• interval: An integer that the specified interval (i.e., number of days) to add to the target timestamp or expression. A negative integer subtracts the interval.
• {timestamp field | expression}: A reference to a timestamp type field in your dataset or an expression that results in a timestamp like the function TODAY() which returns a timestamp representing the current date.

Example 1: Add 7 days to the Created Date:

  DATE_ADD ( "DAY", 7, [Created Date] )

Example 2: Subtract 3 months from today:

  DATE_ADD ( "MONTH", -3, TODAY() )

Date Diff

DATE_DIFF returns the difference in days between the date part values of two timestamp fields (or expressions that result in a timestamp).

Syntax:

  DATE_DIFF ( "DAY", {timestamp field|expression}, {timestamp field|expression} )

Arguments:

• {timestamp field | expression}: A reference to a timestamp type field in your dataset or an expression that results in a timestamp like the function TODAY() which returns a timestamp representing the current date.

Example 1: Calculate the sales cycle length of an opportunity as the difference between Created Date and Close Date:

  DATE_DIFF ( "DAY", [Created Date], [Close Date] )

Example 2: Calculate the current age of an opportunity as the difference between Created Date and today:

  DATE_DIFF ( "DAY", [Created Date], TODAY() )

Date Part

DATE_PART extracts datepart values from a timestamp. The DATE_PART function is often used to format the results of other timestamp functions for display purposes.

Syntax:

  DATE_PART ( datepart, timestamp field )

Arguments:

• datepart: The specific part of the date value that you want to extract from the timestamp. Supported values are: YEAR, QTR, MONTH, WEEK, DAY, DATE or EXACT_DATE.
• timestamp field: A reference to a timestamp type field in your dataset.

Example 1: Return the year datepart from a timestamp field named Close Date:

  DATE_PART ( "YEAR", [Close Date] )

Example 2: Return the date datepart representing the first day of the current quarter:

  DATE_PART ( "DATE", DATE_TRUNC ( "QTR", TODAY() ) )

Date Trunc

DATE_TRUNC truncates a timestamp value based on the date part that you specify. For example, DATE_TRUNC can be used to return the first day of a year, the first day of a month, or the Monday of a week.

Syntax:

  DATE_TRUNC ( datepart, timestamp field )

Arguments:

• datepart: The specific part of the date value that you want to truncate to the beginning of. Supported values are: YEAR, QTR, MONTH, WEEK, DAY, DATE or EXACT_DATE.
  Note: When using the QTR or WEEK dateparts, the DATE_TRUNC function will return an epoch value. You can use the DATE_PART function to format the results for display as shown in the example below.
• timestamp field: A reference to a timestamp type field in your dataset.

Example 1: Return the first day of the year from values in the timestamp field named Close Date:

  DATE_TRUNC ( "YEAR", [Close Date] )

Example 2: Use the DATE_PART function to return the friendly week name from values of the timestamp field named Close Date:

  DATE_PART ( "WEEK", DATE_TRUNC ( "WEEK", [Close Date] ) )

Group

The GROUP statement is a conditional expression, similar to CASE statement, except that it allows values to meet more than one condition. Each condition in the GROUP statement is evaluated based on a Boolean expression, and the GROUP statement returns the specified result value for the ALL conditions that evaluate to true. The All value (optional) allows you to specify a result to return for all rows.

The GROUP statement is useful for defining 'bucketing' fields that allow overlapping conditions, where each row in the query results could fall into more than one of the buckets defined by the boolean conditions.

Because the GROUP statement can result in the same row appearing in multiple buckets, be sure to review your report results thoroughly to ensure they accurately reflect the information you are trying to convey and are not misleading to other viewers of the report.

Syntax:

  GROUP ( 
boolean condition 1, result 1,
boolean condition 2, result 2,
...
boolean condition n, result n,
ALL value 
)

Arguments:

• boolean condition: A boolean condition is an expression that evaluates to true or false using Boolean Operators or Boolean Functions (i.e., IS_NULL, IS_NOT_NULL).
•  result: Determines the value to return when the corresponding boolean condition evaluates to true.
  Note: All instances of your result arguments need to be of the same data type in order for the GROUP statement to function properly.
• ALL value: ALL value is an optional argument that determines the value to return for all rows in the result set of the query. If the ALL value is not specified, the GROUP statement will not return a group representing all values in the result set.

Example: Categorize opportunities into overlapping categories (each opportunity could fall into more than one bucket) with conditions based on Country and Lead Source:

  GROUP (
[Country] = "United States", "Domestic",
NOT [Country] = "United States", "International",
CONTAINS("Marketing", [Lead Source]), "Marketing Sourced",
[Lead Source] = "Outbound", "Sales Sourced",
"All Opportunities"
)

In the example above, an Opportunity with a Country value of 'United Kingdom' and a Lead Source of 'Outbound' would be represented twice in the query results, once in the category of 'International' and again in the category of 'Sales Sourced'.

Is Null

IS_NULL is a boolean function that determines if the value of a field is NULL (empty). The function evaluates to 'true' if the value is NULL, 'false' if the value is not NULL. This function is most often used as part of condition(s) in a CASE function.

Syntax:

  IS_NULL ( {field} )

Arguments:

• field: A reference to a field in your dataset.

Example: Replace NULL values of the Lead Source field with a string "Unknown":

  CASE ( IS_NULL ( [Lead Source] ), "Unknown", [Lead Source] )

Is Not Null

IS_NOT_NULL is a boolean function that determines if the value of a field is not NULL (empty). The function evaluates to 'true' if the value is not NULL, 'false' if the value is NULL. This function is most often used as part of condition(s) in a CASE function.

Syntax:

  IS_NOT_NULL ( {field} )

Arguments:

• field: A reference to a field in your dataset.

Example: Replace NULL values of the Lead Source field with a string "Unknown":

  CASE ( IS_NOT_NULL ( [Lead Source] ), [Lead Source], "Unknown" )

Last Day

The LAST_DAY function returns the value of the field on the last snapshot date in the result set. This function requires that Snapshot Date is an attribute on the report.

Syntax: 

  LAST_DAY ( {numeric expression} )

Arguments:

• numeric expression: A metric of a field in your dataset.

Example: Calculate the accuracy of your forecasted likely revenue.

  [Likely]/LAST_DAY ( SUM ( [Likely] ) )

Maximum

The MAXIMUM function returns the maximum value of the specified number field. The MAXIMUM function does not currently support fields with a data type other than NUMBER.

Syntax:

  MAXIMUM ( {number field} )

Arguments:

• number field: A reference to a number type field in your dataset.

Example: Return the maximum number from the field Age:

  MAXIMUM ( [Age] )

Minimum

The MINIMUM function returns the minimum value of the specified number field. The MINIMUM function does not currently support fields with a data type other than NUMBER.

Syntax:

  MINIMUM ( {number field} )

Arguments:

• number field: A reference to a number type field in your dataset.

Example: Return the minimum number from the field Age:

  MINIMUM ( [Age] )

Sum

The SUM function returns the sum of values from the specified field. The SUM function does not currently support fields with a data type other than NUMBER.

Syntax:

  SUM ( {number field} )

Arguments:

• number field: A reference to a number type field in your dataset.

Example: Return the sum for all values of the Amount field:

  SUM ( [Amount] )

Today

The TODAY function evaluates to the appropriate date and returns a timestamp with zeroes for the timeparts (i.e., 12:00 AM based on the timezone for your tenant).

Syntax:

  TODAY ("YYYY-MM-DD")

Arguments:

• "YYYY-MM-DD": Optional date string used to pass a specific date field. If not included, TODAY reference your current date in your current timezone.

Example: Calculate the current age of an opportunity as the difference between Created Date and today:

  DATE_DIFF ( "DAY", [Created Date], TODAY() )

Total

The TOTAL function returns the grand total of the values from the specified field within the context of the report. The TOTAL function works only with number fields.

Syntax:

  TOTAL ( {number field} , {field} )

Arguments:

• number field: A reference to a number type field in your dataset.
• field: An optional reference to a field used in your report. Multiple fields can be used in a comma-separated list. When included, the number field is summed over all values of the field in the report. 

Example: Return the percent of total calculation:

  SUM ( [Likely] )/TOTAL ( [Likely] )

Note: To calculate the percentage of the total based on a non-number field (e.g., a count of distinct values in a text field), create another calculated field called One with calculation 1. This gives every record a value of 1 for that field, so TOTAL ( [One] ) provides the total number of records for your subtotal or grand total (displayed for the pivot table based on the options in the Formatting panel). Then you can calculate, e.g., SUM ( [One] )/TOTAL ( [One] ) to find the percentage of a total number of records.

Window

The WINDOW function allows you to create calculated fields that perform advanced analytic business queries on a data frame, or 'window' of the query results, and return a value for every row in that window.

Syntax:

  WINDOW ( aggregate expression, LIST(partitioning fields..., frame_start, frame_end )

Arguments:

• aggregate expression: This argument defines the aggregate calculation you want to perform on each frame of the window.
• LIST(partitioning fields..): This is an optional argument allowing you to explicitly define one or more fields to partition the calculated results (defined in the aggregate_expression) by. If not specified, the WINDOW function will attempt to dynamically determine the appropriate fields to partition the calculated results by based on the layout of your query. If you explicitly define one or more fields to partition the results by, the fields referenced in this argument must be included in the query layout of your report.
• frame_start: An optional timestamp field or expression defining the start of each window frame you want the aggregate expression to operate on.
• frame_end: An optional timestamp field or expression defining the end of each window frame you want the aggregate expression to operate on.

Example 1: Calculate a cumulative total of Net New ARR:

  WINDOW ( SUM ( SUM ( [Net New ARR] ) ), "UNBOUNDED", 0)

Example 2: Return the amount of Closed Won New ARR this Month on the last day of each week:

  WINDOW (
SUM ( [Closed Won New ARR this Month]),
date_add("DAY", -1, date_trunc("WEEK",[Snapshot Date] ) ), 
date_add("DAY", -1, date_trunc("WEEK",[Snapshot Date] ) )
)

Example 3: Calculate the Average Sales Cycle length for all deals closed in the trailing 90 days from a given Close Date:

  WINDOW (
AVERAGE ( DATE_DIFF ( "DAY", [Created Date], [Close Date] ) ), 
DATE_ADD ( "DAY", -90, [Close Date] ), 
[Close Date]
)

Fiscal Calendar and Functions

The following date-related functions are affected when the fiscal calendar is enabled.

Waterfall Visualization Functions

The following functions are specific to waterfall reports.

Lag

The LAG function calculates the value of a given field N days before the current date.

Syntax:

  LAG ( {field}, integer or FILTER_RANGE function, [ID] )

Arguments:

• field: A reference to a field in your dataset.
• integer or FILTER_RANGE function:
  • integer: An integer number of days before the current date. This requires that Snapshot Date is an attribute on the report and that the report contains the "On Day" filter for Snapshot Date. To create this filter, click on Snapshot Date > Date in the Fields list and select Filter. In the Select filter type section, choose "Relative Dates". In the Set Condition section, choose "this" and "day(s)" so that the condition reads, "Show items when the value in this day(s)". Then click "Apply".
  • FILTER_RANGE: This function is required if you would like to use a filter or slicer to adjust the integer number of days before the end date of the report.
• ID: The field that represents a unique ID for each record, usually ID. This field must be in square brackets.

Example: Calculate the total likely revenue 7 days ago.

  LAG (SUM ( [Likely] ), 7, [ID] )

Filter Range

The FILTER_RANGE function is used with other other timestamp functions like LAG and DATE_ADD. This function requires a filter or slicer be applied to the Snapshot Date field.

Syntax:

  FILTER_RANGE ( [Snapshot Date], integer )

Arguments:

• Snapshot Date: The Snapshot Date field for your dataset.
• Integer: The default value to use if the date range of the filter or slicer is less than one day.

Example: Find the likely value of a record a variable number of days ago determined by a slicer or filter on the Snapshot Date field.

  LAG ( [Likely], FILTER_RANGE ( [Snapshot Date], 7 ), [ID] )