Skip to main content
Gainsight Inc.

Formula Fields in Reporting

Overview

This article explains how admins can create Formula Fields in reports. With Formula Fields in Reporting, you can instantly create a formula field on MDA objects, based on your business requirements. The Formula Fields created in a report can be consumed in other functionalities of Gainsight, like: Dashboards, C360/R360, Success Snapshots, etc.

Currently, in Gainsight, you can also create Formula Fields from Rules Engine and Data Management, and can use them in reports.

Data Types Supported

You can create Formula Fields of the following Data Types:

  1. String
  2. Date
  3. Number

Create Formula Fields

To create Formula Field in a Report:

  1. Navigate to Administration > Analytics > Report Builder.
  2. Click + REPORT to create a new report (or) you can use the existing report, if you wish to.
  3. Select the required Object on which you want to create a report.
  4. Add the required fields to Show me and By.
  5. Click + to create a Formula Field.
    Note: You can create a Formula Field from Show me and By. You can also apply Filters using the newly created Formula Fields.
  6. Click + FORMULA FIELD. The Add Formula Field window appears.
  7. Enter the following details:
    • Field Name: Enter the display name of the Formula Field.
    • Data Type: Select the required Data Type (String or Date or 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 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 ADD to save the Formula Field. 

Overview.gif

Note: If you have created multiple Formula Fields in a report and have used only a few, then while saving the report you will see the following confirmation message:

Error.png

Important

  1. In Formula Fields, you can apply a Function within another Function.

Nested function.gif

  1. You can enter Static values of your choice, while building Formula Fields of Number and String data types. 

Static value.gif

  1. You can hover to the Function and see the description of the specific Function.
  2. You can hover to the Field to see the path of the specific Field. 

Hover.gif 

  1. You can also select Lookup fields while creating a Formula Field.

Limitations

  • Formula fields can be applied only on MDA objects.
  • 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.
  • You cannot create Number Data Type formula field in the By section.
  • Path and Summarized by are not shown in Formula Fields.
  • Configure Aliases is not supported in Formula Fields.

String Formula Use Case 

You can see the following example on creating String formula using Concat function.

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

To perform this in report:

  1. Click the + button.
  2. Click +FORMULA FIELD. The Add Formula Field window appears.
  3. Enter the following details:
  • Field Name: Enter the display name as Name and Industry.
  • Data Type: Select the String data type.
  1. For the Concat function, provide the following information:
  • Delimiter: Choose any of the Values (COMMA, HYPHEN, NONE or SPACE)
  • String 1: Select the Company Name field from the dropdown list.
  • String 2: Select the Industry field from the dropdown list.
  1. Click ADD to save the Formula field.
    You can now see the created Formula field in the report.

String Function.gif

String Formulas

The following functions are allowed on String data type. The complete list of String formulas supported by Report Builder and their description is given below:

  1. Concat (Delimiter, String 1, String 2): Concatenates the selected arguments. For example, Concat (Space, Ken, Peterson) will return “Ken Peterson”.
     
  2. Day Of (Date or DateTime): Converts Date or DateTime into Day. For example, Day Of(3/27/2019) will return “wednesday” as the output.
     
  3. 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.
     
  4. Month Name (Date or DateTime, Format): Converts Date or DateTime into Month with user selected format. For example, Month Name(3/27/2019, MON) will return “MAR” as the output.
     
  5. 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.
     
  6. 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.   
     
  7. To Lower Case (String): Converts the selected argument values to Lowercase. For example, To Lower Case(Gainsight) will return “gainsight” as the output.
     
  8. 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 

You can see the following example on creating Date formula using Date Sub 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 Date Sub function.

To perform this in report:

  1. Click the + button.
  2. Click +FORMULA FIELD. The Add Formula Field window appears.
  3. Enter the following details:
  • Field Name: Enter the display name as CSM Feedback Date.
  • Data Type: Select the Date data type.
  1. For the Date Sub function, provide the following information:
  • Date or DateTime: Select the Renewal Date field from the dropdown list.
  • Value: Enter the value. Here, it is “1”.
  • Interval: Select the Month as interval from the dropdown list.
  1. Click ADD to save the Formula field.
    You can now see the created Formula field in the report.

Date Usecase.gif

Date Formulas

The following functions are allowed on Date data type. The complete list of Date formulas supported by Report Builder and their description is given below:

  1. 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.
     
  2. 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.
     
  3. 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.
     
  4. 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.
     
  5. 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.
     
  6. 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.
     
  7. 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.
     
  8. 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.
     
  9. 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.
     
  10. 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.
     
  11. Today (Date or DateTime): Returns the date on which the report is being run. Generally, this reflects today's date.

Number Formula Use Case

You can see the following example on creating Number formula using Date Diff  function.
For instance, as a CSM you may want to know the number of days a participant has taken to respond to a survey feedback, you can now achieve this by using Date Diff function in the Formula Fields.

The Date Diff function calculates the difference of days between the “Survey Sent Date” and “Responded Date”, and displays the number of days a participant took to respond to a survey.

To perform this in report:

  1. Click the + button.
  2. Click +FORMULA FIELD. The Add Formula Field window appears.
  3. Enter the following details:
  • Field Name: Enter the display name as Participant Response Duration.
  • Data Type: Select the Number data type.
  1. For the Date Diff function, provide the following information:
  • Start Date: Select the Survey Sent Date field from the dropdown list.
  • End Date: Select the Responded Date field from the dropdown list.
  • Interval: Select the Day as interval from the dropdown list.
  1. Click ADD to save the Formula field. You can now see the created Formula field in the report.

Config FF 1.gif

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 participants who took 2 or more days to respond to a survey. You can apply the formula field created as a filter and input the value is greater or equal to 2 days.

Filters.gif

Number Formulas

The following functions are allowed on Number data type. The complete list of Number formulas supported by Report Builder and their description is given below:
 

  1. 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.
     
  2. Length(String): Returns the Number of characters in a specific string. For example, Length(Engineering) will return “11” as the output.
     
  3. 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.
     
  4. 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.
     
  5. 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.
     
  6. 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.
     
  7. 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

 

  • Was this article helpful?