Skip to main content
Gainsight Inc.

Formula Builder in Bionic Rules

This article reviews the Formula fields supported in Bionic Rules. The +Formula Builder button can be seen only when you perform the Transformation tasks. This feature allows you to create different types of formulas so that you don’t have to create the formula on the Object directly in SFDC or MDA.

Key Objectives

  • Allows you to build a numerical expression on fields with all arithmetic operators.
  • Enables you to generate a formula on the columns of a single record.
  • Enables you to generate a complex formula on multiple record sets.
  • Enables you to perform complex functions such as SUM, AVG, or AGGREGATE of a column in a set of rows.

Types of Formulas

Following are the types of formulas used in Formula Builder in Bionic Rules:

Numeric Formulas

  • Admin Page Views + Config page views
  • Current Score - Previous Score
  • NPS Avg * 10
  • ((Current Score - Previous Score) / Previous Score) * 100

Trend Formulas in Rules Engine

  • Moving average of a metric over a time window
  • Trend in percentage (time window, Average out past N windows)

See Bionic Rules Trend Formulas for more information.

Sample Expression Syntax

  • <FIELD/VALUE> <OPERATOR> <FIELD/VALUE>

  • <FUNCTION>(<FIELD/VALUE>,[<FIELD/VALUE>/<METHOD>(...)])

LIMITATIONS

  • Expression cannot be added when GroupBy field is added to task.
  • Constants in Formula Field are not considered as token.
  • Formula time span cannot exceed 2 years
  • Trend or Trend in Percentage formula cannot be created unless a date identifier is present.
  • Negative values are not allowed in the Function for Trend and Trend in Percentage.
  • ((A-B) * (A+B)) can not be simplified to (A-B) * (A+B) in this release.
  • Trend and Trend in Percentage accept only integers, not decimal values.

Workflow

Add Formula Field

  1. Navigate to Administration > Rules Engine > +RULE (Bionic - Formula Builder Test).

  1. Click NEXT to navigate to Setup Rule screen.

  2. In Setup Rule screen, click the edit icon for the aggregated task (Transformation Task) as shown in the following image.
    Note: You can create a Formula Builder only for Transformation Task.

  1. Select the Source Object (here, Formula Builder Test) and add the required fields in the Show section.

  1. Click +Formula Builder available on the Setup Rule screen. The Formula Field page opens (refer the following image).

  1. Provide the required information in the following list of fields available in the Formula Field page (refer the image below):

  • Output Field Label: This name contains only alphanumeric and underscore characters. The label should always start with an alphabetical character.
  • Output Header: This name is used as CSV Header to process action. This is view only and is generated based on the Output Field Label (but without space).
  • Decimal Places: Number should be between 0 and 9
  • Formula Type: When you add a Formula Field, you have the following Formula Types available: Expression and Function

Expression

All arithmetic operators are considered as Expression. The primary items in Expression are:

  • Operator: *, +, -, and /
  • LHS: Left hand side Token to the operator (Currency Field or Numeric Field)
  • RHS: Right hand side Token to the operator  (Currency Field or Numeric Field)

If any LHS or RHS is missing in the Formula Field, the following message is displayed when you try to SAVE the formula.

  • Arguments: The list of arguments(Tokens) participating in the method for evaluation.
    Note: If tokens are not entered, the following message is displayed.

  • Arguments length: Max number of arguments that this method can accept. Especially in case of multi row scenario

Function

Some calculations were already available in Engagement Rules, but since they are deprecated in the 5.6 release, Gainsight now offers the Formula Builder in Bionic Rules

In the Formula Field window, when you select Function as Formula Type, the following message appears on the screen.

Trend or Trend in Percentage formula cannot be created unless a date identifier is present. Arguments are consumed inside a function.
 

Example: Trend function syntax is as below. In this case, this function has a fixed number of arguments and each argument has its own context.

  • Trend( Period([ DAY / WEEK / MONTH / YEAR ], <Period Length>),
  • <Source Field [ field of type numeric ]>,
  • <Trend Indicator [ field of type date ]>,
  • <Aggregation Method [ SUM,AVG,COUNT, COUNT DISTINCT, MIN, MAX ]>,
  • <Prior Period>,< Adjust Missing Data  [ true/false ]>);

To accommodate this kind of requirements, Argument item is added.

  1. Click SAVE. Clicking RESET erases the data that you entered and allows you to refill the field with fresh information.

In Setup Rule, you can see the newly created Formula Field is added in the Show section as shown in the following mage.

The same field will also be added in Setup Action > Action type >Field Mappings section.

The following are the defined java based patterns to validate the supported formulas:

Expression Formula Result
A+B days_active+folders_created Pass
A-B days_active-folders_created Pass
A*B days_active*folders_created Pass
A/B case folders_created when 0 then 0 else days_active/folders_created end Pass
(A+B)-C (days_active+folders_created)-link_clicks Pass
A+B+C days_active+folders_created+link_clicks Pass
A+(B*C) days_active+(folders_created*link_clicks) Pass
(A+B)*C (days_active+folders_created)*link_clicks Pass
A+(B/C) case link_clicks when 0 then 0 else days_active+(folders_created/link_clicks) end Pass
(A+B)/C case link_clicks when 0 then 0 else (days_active+folders_created)/link_clicks end Pass
A-(B*C) days_active-(folders_created*link_clicks) Pass
(A-B)*C (days_active-folders_created)*link_clicks Pass
(A-B)/C case link_clicks when 0 then 0 else (days_active-folders_created)/link_clicks end Pass
A*(B/C) case link_clicks when 0 then 0 else days_active*(folders-created/link_clicks) end Pass
A+B-(C*D) days_active+folders_created-(link_clicks*page_views) Pass
A+(B-C)*D days_active+(folders_created-link_clicks)*page_views Pass
A*B case (days_active*folders_created) when 0 then 1 else days_active*folders_created end Pass
A*B case (days_active*folders_created) when 0 then 1 else null end  
A/1204 days_active/1024 Not Supported
((A/B) + (C/B)) case folders_created when 0 then 0 else (days_active/folders_created)+(link_clicks/folders_created) end Pass
((A/B) + (C/B)) case folders_created when 0 then Null else (days_active/folders_created)+(link_clicks/folders_created) end Pass
A CASE WHEN A BETWEEN 1 AND 50 Then 3 When A Between 51 And 100 Then 5 When A > 100 Then 10 Else 0 End Not Supported


 

  • Was this article helpful?