Skip to main content
Gainsight Inc.

Formula Fields in Reporting

Gainsight NXT

 

This article explains the Admins on how to create Formula Fields in reports.

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.

Data Types Supported

The following are the Data Types supported in Formula Fields:

  • String
  • Date
  • Number
  • Percentage

Create Formula Fields

To create Formula Fields:

  1. Navigate to Insights > Reports.
  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.
    Note: Formula fields cannot be used in reports created on SFDC objects.
  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. Select the percentage data type to view the output in the percentage format.
      • Functions and Fields may differ based on the selection of the data type.
    3. Select the required Function of your choice, and then enter the Values and/or Fields as per your requirement.
  6. Click Save.

Add formula 1.jpeg

Note:

  • The Formula Field created is displayed in the Fields section on the left pane
  • 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.

Add formula 3.jpeg

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

Add formula 4.jpeg

  • 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.
  • Formula fields cannot be used in reports created on SFDC objects.
  • Percentage data type is not groupable while creating reports.

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 String Formula.
    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 [Industry] field from the dropdown list.
    3. String2: Select theCompany Name field from the dropdown list.
  4. Click Save.

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

Add formula 5.jpeg

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 Date Formula.
    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 “0”.
    3. Interval: Select the Day 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 Formula use case.jpeg

 

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 Number Field Formula.
    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 Company [Created Date] field from the dropdown list.
    2. End Date: Select the Company [Modified 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.

Number Formula use case.jpeg

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. you can choose Milliseconds as an interval, which ensures an enhanced level of precision in date calculations by calculating the difference in milliseconds between start and end dates.
  • 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.
  • Sum(Field): Returns the sum of the selected field/column. For example, Sum(Company [ARR)] returns the sum of the ARR column which is the total ARR.
  • Average(Field): Returns the average of the selected field/column. For example, Average(Company[ARR]) returns the average of ARR for the selected companies.
  • Count(Field): Returns the count of the selected field/column. 
  • Count Distinct (Field): The Count Distinct ensures the counting of unique IDs only, preventing the inclusion of duplicate entries in reports.

The percentage data type lets you view the output in a percentage format. You need to select the  percentage option from the Data Type dropdown, while creating the Formula field. For example, consider that you want to derive the contribution of  CSMs to the company’s revenue in percentage. You can create a formula that calculates the required output as follows:

  1. Click Add Formula Field. The Add Formula Field page appears.
  2. Enter the following details:
  • Label: Enter the name of the Formula Field. In this case Renewal Days.
  • Data Type: From the Data Type dropdown list, select Percentage.
  1. In the formula textbox, select the following functions: SUM ( Revenue) ) / COUNT ( CSM ) 
  2. Click Save
  3. The Formula field created is displayed in the Fields section on the left pane.

Note

  • From the Function drop-down menu in the Add Formula Field page, you can choose the Count Distinct option, it ensures the counting of unique IDs only, preventing the inclusion of duplicate entries in reports.
  • From the Function drop-down menu in the Add Formula Fields, you can choose the Date Difference option, which allows the easy calculation of the average difference between two date fields. Also, you can choose Milliseconds as an interval, which ensures an enhanced level of precision in date calculations by calculating the difference in milliseconds between start and end dates.

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

Additional Resources