Formula Fields on CTA, Task and Success Plan Objects
IMPORTANT - Articles Impacted due to 6.42 July CS Release
Due to the v6.42 July, 2024 release, this article has been impacted. Steps, images, and playable GIFs in this article will soon be updated to reflect the latest changes.
Overview
This article explains how admins can create Formula Fields in Cockpit and Success Plans, by using String and Date functions. With Formula Fields, you can instantly create a formula field on CTA, Task and Success Plan objects, based on your business requirements. The formula fields created on these objects can be consumed in Cockpit Detail View page, Reports, etc.
Use Case: For instance, a CSM wants to see the number of days left to close an Opportunity assigned, admin can create a formula field as described below in the Date Function section, and then add it to the CTA Detail View so that CSMs can view the details from the Cockpit page.
Create Formula Fields on CTA, Task and Success Plan Objects
- String, Date, and Numeric functions are supported in Gainsight, to build a calculated field.
- In a string function, you can use any field with Data type Email, GS ID, Rich Text Area, SFDC ID, String, and URL.
- In a Date Function, you can use any field which has Data type, Date or Datetime.
- In a string function, you can use any field with Data type Email, GS ID, Rich Text Area, SFDC ID, String, and URL.
- In a Date Function, you can use any field which has Data type, Date or Datetime.
- To use a formula function in a custom field, you must ensure that the Data type of the custom field is same as the return type of the formula function. For example, the Date Diff function returns output in Number format. To use this function, your custom field must have data type as either Number, Percentage, or Currency. Substring function returns output in String format. You cannot use it if your custom field’s data type is currency or Date.
List of Supported Formula Functions in Gainsight:
String Functions
Formula Name | Syntax | Return Type | Description | Example |
---|---|---|---|---|
Substring | Substring (String field, character position from where extraction must begin, no. of characters to be extracted) | String | String This function allows you to extract a part of the string from the original string. You must provide the position of the character in the string from where the extraction must begin and the number of characters upto which the substring must be extracted. | Substring (Prashant,4,3) returns sha. The extraction begins from fourth position and three characters are selected for extraction. |
Length | Length (string field) | Number | This function returns the number of characters in a string. Blank spaces and special characters are also taken into account. | Length (Prashant) returns 8, since the string has eight characters. |
To Lowercase | To Lower Case (String Field) | String | This function returns the string by converting all the characters into lowercase letters. | To Lower Case(Prashant) returns prashant |
To Uppercase | To Upper Case (String Field) | String | This function returns the string by converting all the characters into uppercase letters. | To Upper Case(Prashant) returns PRASHANT. |
String Position | String Position (sub string field, String field) | Number | This function returns the position of the first character of the substring in the main string. | String Position (hant, Prashant) returns 5. The substring here is hant and the first character of this substring is in the 5th position in the main string. |
Left String | Left String ( String field, number of characters to be extracted) | String | This position extracts a substring from the main string. You must specify the number of characters to be included in the sub string. The character count begins from the left side of the string. | Left String (Prashant, 4) returns Pras. The string length is 8. Number of characters specified is 4. So the count starts from left of string and four characters are extracted. |
Right String | Right String ( String field, number of characters to be extracted) | String | This position extracts a substring from the main string. You must specify the number of characters to be included in the sub string. The character count begins from the right side of the string. | Right String (Prashant, 4) returns hant. The string length is 8. Number of characters specified is 4. So the count starts from right side and four characters are extracted. |
Concat | Concat (delimiter, first String field, second string field) | String | This function adds two strings to convert them into a single string. You must provide a delimiter between the two strings. You can use either a comma, space or hyphen as the delimiter. | Concat (hyphen,Prashant, Mathapathi) returns Prashant-Mathapathi, since hyphen is used as the delimiter between the two strings. |
Date Functions
Formula Name | Syntax | Return Type | Description | Example |
---|---|---|---|---|
Date Diff |
Date Diff (subtrahend date field, minuend date field, interval) |
Number |
This function accepts two dates, subtracts the first date from the second date and returns the difference in a specified interval. Interval can be days, months, quarters, weeks, and years. If the first date is greater than the second date, the result is displayed as a negative numeral |
DateDiff (04/25/2019, 04/30/2019, days) returns 5. |
Date Add |
Date Add (Date or DateTime field , numeric value, interval of the numeric value) |
Date |
This function returns a date after adding a specific number of days, weeks, months, or quarters to the given date. You must provide a date, followed by a number of be added, and the interval for the number. Interval can be either Day, Quarter, Month or week. |
Date Add (01/07/2018, 4, week) returns 02/04/2018. Input date is 7th Jan. Four weeks are added to this date and the result is 4th feb. |
Date Sub |
Date Add (Date or DateTime field , numeric value, interval of the numeric value) |
Date |
This function returns a date after subtracting a specific number of days, weeks, months, or quarters from the given date. You must provide a date, followed by a number of be subtracted, and the interval for the number. Interval can be either Day, Quarter, Month or week. |
Date Sub (01/07/2018, 4, Day) returns 01/03/2018. Input date is 7th Jan.After subtracting 4 days, the output is 3rd Jan. |
First Day of Week |
First day of Week (Date field) |
Date |
This function accepts a date and returns the date on which the week started for the given date. Monday is considered to be the start date of the week. |
First Day of Week (04/28/2019) returns 04/22/2019 , since the week of 28th April started on 22nd april (Monday). |
Last Day of Week |
Last Day of the Week (Date field) |
Date |
This function accepts a date and returns the date on which the week ends for the given date. Sunday is considered to be the last date for the week. |
Last Day of Week (04/28/2019) returns 05/25/2019 , since the week of 28th April ends on 5th May (Sunday). |
Last Day of Quarter |
Last Day of the Week (Date field) |
Date |
This function accepts a date and returns the date on which the quarter ends for the given date.The last day of the quarter is considered to be the last date of the quarter. |
Last Day of Quarter (04/28/2019) returns 06/30/2019. |
Month Num |
Month Num (Date field) |
Number |
This function returns the number of the month to which the given date belongs to. |
Month Num (04/29/2019) returns 4. |
Month Name |
Month Name (Date field, format) |
String |
This function returns the name of the month for a given date. You can select the format in which the month name must be displayed. |
Month Name (04/29/2019, Mon), returns Apr. If Month was selected as output format, April would have been displayed. |
Week Num |
Week Num (Date field) |
Number |
This function returns the number of the Week (in the year) to which the given date belongs to |
Week Num (04/29/2019) returns 17 because the given date falls in the 17th week of 2019 year. |
Week in Month |
Week in Month (date field) |
Number |
This function returns the number of the week in a month to which the given date belongs to |
Week In Month (04/29/2019) returns 5, since the given date belongs to the 5th week of April |
Year Num |
Year Num (date field) |
Number |
This function returns the year to which the given date belongs to |
Year Num (04/29/2019) returns 2019 |
Day of |
Day of (Date field) |
String |
This function returns the day of the week for a given date. |
Day of (04/29/2019) returns monday, since the given date falls on a a monday. |
First Day of Month |
First Day of the Month (Date field) |
Date |
This function accepts a date and returns the date on which the month begins for the given date. The first day of the month is considered to be the start of the month. |
First Day of Month (04/29/2019) returns 01/04/2019. |
First Day of Quarter |
First Day of Quarter (Date field) |
Date |
This function accepts a date and returns the date on which the quarter begins for the given date. The first day of the quarter is considered to be the start of the quarter. |
First Day of Quarter (04/29/2019) returns 01/04/2019. |
First Day of Year |
First Day of Quarter (Date field) |
Date |
This function accepts a date and returns the date on which the year begins for the given date. The first day of the year is considered to be the start of the year. |
First Day of Year (04/29/2019) returns 01/01/2019 |
Last Day of Month |
First Day of Quarter (Date field) |
Date |
This function accepts a date and returns the date on which the month ends for the given date. Last day of the month is considered to be the last date of the month. |
Last Day of Month (04/29/2019) returns 04/30/2019. |
Last day of Quarter |
First Day of Quarter (Date field) |
Date |
This function accepts a date and returns the date on which the quarter ends for the given date. Last day of the quarter is considered to be the last date of the quarter. |
Last Day of Quarter (04/29/2019) returns 06/30/2019. |
Last day of Year |
First Day of Quarter (Date field) |
Date |
This function accepts a date and returns the date on which the year ends for the given date. Last day of the year is considered to be the last date of the quarter. |
Last Day of Year(04/29/2019) returns 12/31/2019. |
Create Calculated Field with String Function
This section shows how to use the Concat Function to merge two string fields into a single field. For instance, if you have two custom fields called CTA Name and Status, you can merge them using the Concat function to create a new field that stores the CTA Name and status. After a formula field is created, you can then add this field to the CTA Detail view so that the CSMs can view the details of the newly created formula field, from the Cockpit page.
To create a String calculated field:
- Navigate to Administration > Call to Action (CTA).
Notes:
- To create formula fields on Task object, navigate to Administration > Task.
- To create formula fields on Task object, navigate to Administration > Success Plans.
- Click + CUSTOM FIELD.
- Enter the display name in the Display name text box.
- From the Data type field, select a value (here String).
- Enable the Calculated field checkbox.
- Click CREATE FORMULA.
- Click the click here link.
- Drag and drop the required function from the Search Fields/Functions section. Alternatively, you can also select the required function from the drop-down menu.
- Build the formula field as shown below and click SAVE.
Click TEST FORMULA to verify if the formula is working as expected.
Create Calculated Field with Date Function
This section shows how to use the Date functions. For instance, the Call to Action object has a date field called Opportunity Created Date which stores the opportunity created date for the customer. Generally, opportunities which are due in the current quarter are given high priority. You can use the Date diff function to calculate the number of days left for the opportunity to close in the current quarter. After a formula field is created, you can then add this field to the CTA Detail view so that the CSMs can view the number of days left/days due for the opportunity to close, from the Cockpit page.
Follow the procedure mentioned in the previous section, up to step 8. In step four select Number instead of String.
ADVANCED FORMULA builder helps the Admins to create complex formulas like performing arithmetic operations and case statements.
Case statements
Examples:
- case when A>0 then 1 else 0 end
- case when A-B>=0 then A-B else C-B end
- case when A=B then 1 else 0 end
- case when A is null then 0 else B * (180/7) end
- case when (A+B+C+D) = 0 then 0 else ((E+F+G+H+I)/(A+B+C+D)*100) end
Notes:
- Ensure that the syntax is correct. If the syntax is incorrect, you cannot save the formula.
- Validator for advanced formula is being developed.
- Once an advanced formula field is set, reverting to a basic formula is not supported as basic formula builder does not support these operations.
- Gainsight recommends you to write down your formula on paper. If you get it wrong while building the formula in the Formula field, you will have to start all over again. The formula can be edited from right to left using the close icon.