Skip to main content
Gainsight Inc.

Formula Fields in Reporting (Horizon Analytics)

Overview

With Formula Fields functionality in Reporting, admins can instantly create a formula field on Gainsight objects, based on the business requirements. The Formula Fields created in a report can be consumed by other functionalities of Gainsight like Dashboards.

IMPORTANT: Once the Reporting is Generally Available for all customers, the Formula Fields created in a report can be consumed in other functionalities of Gainsight like C360/R360, Success Snapshots, etc.

Data Types Supported

The following are the Data Types supported in Formula Fields:

  • String
  • Date
  • Number

Create Formula Fields

To create Formula Fields:

  1. Navigate to Administration > Report Builder (BETA).
  2. Click on the existing report or click Create Report to create a new report.
  3. Select the required Object on which you want to create a report.
  4. Click Add Formula Field. The Add Formula Field page appears.
  5. Populate the following details:
    1. Label: Enter the name of the Formula Field.
    2. Data Type: From the Data Type dropdown list, select the required data type (String, Date, Number).

      Notes:
  • Select the data type, based on the output field you want. For example, if you want to see the output as number of days/weeks/months, then select the output data type as a number.
  • Functions and Fields may differ based on the selection of the data type.
  1. Select the required Function of your choice, and then enter the Values and/or Fields as per your requirement.
  1. Click Save.

The Formula Field created is displayed in the Fields section on the left pane.

1. Formula fields.png

Drag and drop the newly created Formula Field to Show Me or Group By sections to create a report.

Important

  • In Formula Fields, you can apply a Function with another Function.

  • Enter Static values of your choice while building Formula Fields of Number and String data types.

  • Hover to the Function and see the description of the specific Function.
  • Select Lookup fields while creating a Formula Field.

Limitations

  • You cannot create formula fields on the following objects: Email Logs, Email Raw Events, and Unsubscribed Emails.
  • A maximum of 10 Formula fields can be added to a report.
  • A Formula Field cannot be used inside another formula field.
  • Aggregated fields are not supported.
  • Path and Summarized by are not shown in Formula Fields.
  • Configure Aliases is not supported in Formula Fields.

String Formula Use Case

The following example shows creating a String formula using the Concat function.

The Company Name and CSM Name fields are available for every Company. For instance, you may want to see a single field that includes both Company Name and CSM Name, instead of viewing the company name and CSM Name separately. To achieve this, you can use the String function to concatenate these fields and merge them into a single field called Name and CSM.

To create String Formula:

  1. Click Add Formula Field. The Add Formula Field page appears.
  2. Enter the following details:
    1. Label: Enter the name of the Formula Field. In this case Name and CSM.
    2. Data Type: From the Data Type dropdown list, select String.
  3. For Concat Function, populate the following details:

    1. Delimiter: Choose any of the values ( COMMA, HYPHEN, NONE, SPACE).
    2. String1: Select the Company Name field from the dropdown list.
    3. String2: Select the CSM Name field from the dropdown list.
  4. Click Save.

The Formula field created is displayed in the Fields section on the left pane.

String Formulas

The String Data has the following functions applicable to it. The complete list of String formulas supported by Report Builder and their description is given below:

  • Concat (Delimiter, String 1, String 2): Concatenates the selected arguments. For example, Concat (Space, Ken, Peterson) will return “Ken Peterson”.

  • Day Of (Date or DateTime): Converts Date or DateTime into Day. For example, Day Of (3/27/2019) will return “Wednesday” as the output.

  • Left String (String, No of Characters): Extracts the left part of the string with the appearance of a string character. For example, Left String(Gainsight, 6) will return “Gainsi” as the output.

  • Month Name (Date or DateTime, Format): Converts Date or DateTime into Month with the user-selected format. For example, Month Name(3/27/2019, MON) will return “MAR” as the output.

  • Right String (String, No of Characters): Extracts the right part of the string with the appearance of a string character. For example, Right String(Gainsight, 6) will return “nsight” as the output.

  • SubString (String, Start Position, No of Characters): finds the substring based on the position and no. of characters. For example, SubString(Engineering, 2,4) will return values from 2 to 4, i.e “nginas the output.

  • To Lower Case (String): Converts the selected argument values to Lowercase. For example, To Lower Case(Gainsight) will return “gainsight” as the output.

  • To Upper Case (String): Converts the selected argument values to Uppercase. For example, To Upper Case(Gainsight) will return “GAINSIGHT” as the output.

Date Formula Use Case

The following example shows creating a Date formula using the DateSub function.

For instance, as a CSM manager, you may want to know the feedback of your CSMs services, from your customer, one month before the renewal date. To achieve this, you can use the Date Sub function.

To create this Date Formula:

  1. Click Add Formula Field. The Add Formula Field page appears.
  2. Enter the following details:
    1. Label: Enter the name of the Formula Field. In this case CSM Feedback Date.
    2. Data Type: From the Data Type dropdown list, select Date.
  3. For Date Sub Function, populate the following details:

    1. Date or DateTime: Select the Renewal Date field from the dropdown list.
    2. Value: Enter the value. Here, it is “1”.
    3. Interval: Select the Month as an interval from the dropdown list.
  4. Click Save.

The Formula field created is displayed in the Fields section on the left pane.

Date Formulas

The Date Data has the following functions applicable to it. The complete list of Date formulas supported by Report Builder and their description is given below:

  • Date Add(Date or DateTime, Value, Interval): Add a value to a date in the specified interval. For example, Date Add(3/27/2019, 1, MONTH) will return “4/27/2019” as the output.

  • Date Sub(Date or DateTime, Value, Interval): Subtract a value from a date in the specified interval. For example, Date Sub(3/27/2019, 1, MONTH) will return “2/27/2019” as the output.

  • First Day Of Month(Date or DateTime): Returns the first day of the Month. For example, First Day Of Month(3/27/2019) will return “3/1/2019” as the output.

  • First Day Of Quarter(Date or DateTime): Returns the first day of the Quarter. For example, First Day Of Quarter(3/27/2019) will return “1/1/2019” as the output.

  • First Day Of Week(Date or DateTime): Returns the first day of the Week. For example, First Day Of Week(3/27/2019) will return “3/25/2019” as the output.

  • First Day Of Year(Date or DateTime): Returns the first day of the Year. For example, First Day Of Year(3/27/2019) will return “1/1/2019” as the output.

  • Last Day Of Month(Date or DateTime): Returns the last day of the Month. For example, Last Day Of Month(3/27/2019) will return “3/31/2019” as the output.

  • Last Day Of Quarter(Date or DateTime): Returns the last day of the Quarter. For example, Last Day Of Quarter(3/27/2019) will return “3/31/2019” as the output.

  • Last Day Of Week(Date or DateTime): Returns the last day of the Week. For example, Last Day Of Week(4/1/2020) will return “4/5/2020” as the output.

  • Last Day Of Year(Date or DateTime): Returns the last day of the Year. For example, Last Day Of Year(4/1/2020) will return “12/31/2020” as the output.

  • Today (Date or DateTime): Returns the date on which the report is being run. Generally, this reflects today's date.

Number Formula Use Case

The following example shows creating a Number formula using the Date Diff function.

For Instance, as a CSM you may want to know the number of days remaining for the Company Subscription renewal, you can now achieve this using the Date Diff function in the Formula Fields.

The Date Diff function calculates the difference of days between the “Renewal Date” and “Original Contract Date”, and displays the number of days remaining.

To create this Number Formula:

  1. Click Add Formula Field. The Add Formula Field page appears.
  2. Enter the following details:
    1. Label: Enter the name of the Formula Field. In this case Renewal Days.
    2. Data Type: From the Data Type dropdown list, select Number.
  3. For Date Diff Function, populate the following details:

    1. Start Date: Select the Original Contract Date field from the dropdown list.
    2. End Date: Select the Renewal Date field from the dropdown list.
    3. Interval: Select the Day as interval from the dropdown list.
  4. Click Save.

The Formula field created is displayed in the Fields section on the left pane.

Users can also apply this formula field as a filter based on their business needs. For example, if you want to look into all the renewals within 2 or more days. You can apply the formula field created as a filter and input the value is greater or equal to 2 days.

Number Formulas

The Number Data has the following functions applicable to it. The complete list of Number formulas supported by Report Builder and their description is given below:

  • Date Diff(Start Date. End Date, Interval): Returns difference of the two selected dates in the specified interval. For example, Date Diff(3/27/2019, 4/1/2020, MONTH) will return “13” as the output.

  • Length(String): Returns the Number of characters in a specific string. For example, Length(Engineering) will return “11” as the output.

  • Month Num(Date or DateTime): Returns the number of the specific month. For example, Month Num(3/27/2019) will return “3” as the output.

  • String Position(Substring, String): Returns the position of the first occurrence of a substring in the given string. For example, POSITION(“doe” in “johndoe”) returns 5. Counting of the position starts with an index 1.

  • Week in Month(Date or DateTime): Returns the week number for a given date in that month. For example, Week in Month(3/27/2019) will return “4” as the output.

  • Week Num(Date or DateTime): Returns the week number for a given date in the current year. For example, Week Num(3/27/2019) will return “13” as the output.

  • Year Num(Date or DateTime): Returns the year for a given date. For example, Year Num(3/27/2019) will return “2019” as the output.

Null Values in Formula Fields

Below are the examples on how Null values are handled in Formula Fields:

Field 1 Value Field 2 Value Operator Result
1 Null + 1
1 Null - 1
1 Null * 0
1 Null / 0
Null 1 + 1
Null 1 - -1
Null 1 * 0
Null 1 / 0